Time Intelligent with slicer selection

Hey chethan ,

 

please let me recommend this article as this is about time patterns using DAX: https://www.daxpatterns.com/time-patterns/

First I created a dedicated calendar table using this DAX statement:

 

calendar = 
var DateStart = MIN('Data'[Period Date])
var DateEnd = MAX('Data'[Period Date])
return
ADDCOLUMNS(
    CALENDAR(DateStart , DateEnd)
    , "Year-MM" , FORMAT(''[Date] , "YYYY-MM")
    , "Year" , FORMAT(''[Date] , "YYYY")
)

 

Then I created this base measure

 

Total Revenue = SUM(Data[Revenue])

 

Based on the above I excluded the flag 0 using this DAX:

 

Total Revenue w/o 0 = 
CALCULATE(
    [Total Revenue]
    , 'Data'[Flag] <> 0
) 

 

I used this DAX to create the revenue for the previous period, of course also without the flag 0:

 

Total Revenue w/o 0 - prev Month = 
CALCULATE(
    [Total Revenue w/o 0]
    , PREVIOUSMONTH('calendar'[Date])
)

 

All of this allows me to create this report:

Just create the finall measure that calculates the difference between the current and the previous period.

Based on the data you provided and the default behavior DAX is treating nulls, I'm wondering if a BLANK flag should be treated as 0 (this is what happend by default).

 

Regards,

Tom

Hi,

You may download my solution PBI file from here.

Hope this helps.