Time Intelligence Functions scenario : Calculating YTD,QTD,MTD,Previous month sales,Total Sales, for a User selected period(12 months/24 months) In a single column Using slicer selection. (Power BI interactive report included in the end of this post)

 Time Intelligence Functions Scenario

Requirement: Calculating YTD,QTD,MTD,Previous month sales,Total Sales, for a User selected period(12 months/24 months) In a single column Using slicer selection.

(Power BI interactive report included in the end of this post) 

Source Tables: sales table with order date and sales amount

Relationships: Created the Dimdate table and established a relationship with the date in Dimdate and order date in the sales table.

Created a Total Sales measure:

Total Sales = SUM(Sales[SalesAmount])

Using the above Total Sales measure

Created a MTD Sales measure:

MTD Sales = CALCULATE([Total Sales],

DATESMTD(Dimdate[Date]))

Created a QTD Sales measure:

QTD Sales = CALCULATE([Total Sales],

DATESQTD(Dimdate[Date]))

Created a QTD Sales measure:

YTD Sales = CALCULATE([Total Sales],

DATESYTD(Dimdate[Date]))

Created a Previous Month Sales measure:

Previous months Sales = CALCULATE([Total Sales],

PREVIOUSMONTH(Dimdate[Date]))

Created the Same Period Last Year Sales measure:

Same Period Last Year sales = CALCULATE([Total Sales],

SAMEPERIODLASTYEAR(Dimdate[Date]))

created a month selection table with one column and two rows (values:12,24) for user month selection

created a table for sales mode in which all the created measures names were given as row values.

Sales Modes Table = {"MTD SALES","QTD SALES","YTD SALES",

                        "PREVIOUS MONTH SALES","SAME PERIOD LAST YEAR SALES"}

Created a measure "Selection Sales_Mode Sales" in I assigned the respective measures to

the user selected

sales modes values using SELECTEDVALUE Fn

Selection Sales_Mode Sales =

var selection= SELECTEDVALUE('Sales Modes Table'[Value])

var Result=SWITCH(selection,

"YTD SALES", [YTD Sales],

"QTD SALES",[QTD Sales],

"MTD SALES",[MTD Sales],

"PREVIOUS MONTH SALES",[Previous months Sales],

"SAME PERIOD LAST YEAR SALES",[Same Period Last Year sales],

[Total Sales])

return Result

Created a table "Selected Mode Sales" which is the main logic in this to display

all the selected sales modes

in one column.

logic: calculated user selected days from last sales date into a

variable named last12monthsperiod Calculated the respective selected sales mode sales

within the user-selected months period in a variable named SELECTEDSALES

        returned the SELECTEDSALES if the user selected a month period else returned

the measure [Selection Sales_Mode Sales]

Selected Mode Sales =

var last12monthsperiod=DATESINPERIOD(Sales[OrderDate],[last Sale date],

        -(SELECTEDVALUE('MONTH SELECTION'[MONTH SELECTION])),MONTH)

var SELECTEDSALES=CALCULATE([Selection Sales_Mode Sales],

        (last12monthsperiod))

return IF(HASONEVALUE('MONTH SELECTION'[MONTH SELECTION]),

        SELECTEDSALES,[Selection Sales_Mode Sales])

Created a measure "DYNAMIC TITLE" for Dynamic title for the Table visual

DYNAMIC TITLE =

var MONTHSELECTIONVALUE=SELECTEDVALUE('MONTH SELECTION'[MONTH SELECTION])

var MONTH_SELECTION = IF(HASONEVALUE('MONTH SELECTION'[MONTH SELECTION]),"FOR "

& MONTHSELECTIONVALUE & " MONTHS","")

var SALESMODESELVALUE=SELECTEDVALUE('Sales Modes Table'[Value])

var SALES_SELECTION = IF(HASONEVALUE('Sales Modes Table'[Value]),

SALESMODESELVALUE,"TOTAL SALES")

return SALES_SELECTION &" "& MONTH_SELECTION

Created a Measure "MESSAGE BOX"for the Message Box (user suggestions/Information About The selections)

MESSAGE BOX=

var MONTHSELECTIONVALUE=SELECTEDVALUE('MONTH SELECTION'[MONTH SELECTION])

var MONTH_SELECTION = IF(HASONEVALUE('MONTH SELECTION'[MONTH SELECTION]),

"Selected Months Are " & MONTHSELECTIONVALUE,"Default Selection Value is ALL")

var m_message1=IF(COUNTROWS(VALUES('MONTH SELECTION'[MONTH SELECTION]))<>1,

"Select One Month Selection Value","")

var SALESMODESELVALUE=SELECTEDVALUE('Sales Modes Table'[Value])

var SALES_SELECTION = IF(HASONEVALUE('Sales Modes Table'[Value]),

"Selected Sales Mode is " & SALESMODESELVALUE,"Default Selected Sales Mode is TOTAL SALES")

var s_message2=IF(COUNTROWS(VALUES('MONTH SELECTION'[MONTH SELECTION]))<>1,

"Select One Sales Mode Value","")

return s_message2

&SALES_SELECTION & m_message1 &MONTH_SELECTION

created 4 measures taking each variable as a measure from the above Mesure

for ease of access and displayed them in TEXT BOX visual

(* not in a card visual...I Used Text Box visual to Show these measures)

conditional formatted the text box title colour based on user selection.

created page 1 with one column for all selected sales mode sales and one column for total sales for reference and both the slicers for user selection.

All the selected sales modes sales are shown in a table in  the Power BI report page 2 with a slicer for user selection

Please View the interactive  Power bi report below.

Click on "view interactive content" on the Below report to Experience the functionality of Power Bi Report

Please Follow and Share

Thanking You🙏

P.M.Prasad