Year, Month and Day change comparison

[Anonymous]

Hi,

I have two cards and three filters. One of the cards is current and another one is the previous one. The filters are Year, Month and Day.

Let's say all filters are on "all", if i clicked on the year 2023, the cards will show data on 2023 and 2023 respectively. Then, let's say if i clicked on March on Month filter, the cards will show data from March 2023 and February 2023 respectively. Same with Day filter, if i clicked on 15, cards will display 15 March 2023 and 14 March 2023.

Here is my current DAX for the second card (previous year/month/day):

Change Compare =

IF(SELECTEDVALUE(Sales[createdon].[Year]) <> BLANK() && SELECTEDVALUE(Sales[createdon].[Month]) == BLANK() && SELECTEDVALUE(Sales[createdon].[Day]) == BLANK(),

CALCULATE([Sales], FILTER(ALL(Sales), Sales[createdon].[Year] = SELECTEDVALUE(Sales[createdon].[Year])-1)),

IF(SELECTEDVALUE(Sales[createdon].[Year]) <> BLANK() && SELECTEDVALUE(Sales[createdon].[Month]) <> BLANK() && SELECTEDVALUE(Sales[createdon].[Day]) == BLANK(),

CALCULATE([Sales], FILTER(ALL(Sales), Sales[createdon].[Month] = SELECTEDVALUE(Sales[createdon].[Month])-1)),

IF(SELECTEDVALUE(Sales[createdon].[Year]) <> BLANK() && SELECTEDVALUE(Sales[createdon].[Month]) <> BLANK() && SELECTEDVALUE(Sales[createdon].[Day]) <> BLANK(),

CALCULATE([Sales], FILTER(ALL(Sales), Sales[createdon].[Day] = SELECTEDVALUE(Sales[createdon].[Day])-1)), BLANK())))

This code works but it is not perfect. Can you guys assist me with this?

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you use three slicers to compare the current and prevous value .

Here is my understand for your need :

(1)This is my test data:

(2)We can create a measure like this:

Previous = var _year_slicer =IF( ISFILTERED('Sales'[createdon].[Year]) ,1,0)
var _month_slicer =IF(  ISFILTERED('Sales'[createdon].[Month]),1,0)
var _day_slicer = IF( ISFILTERED('Sales'[createdon].[Day]),1,0)
var _curent_year = MAX('Sales'[createdon].[Year])
var _previous_year  =SUMX( FILTER( ALL('Sales') , 'Sales'[createdon].[Year] = _curent_year-1) , [Value])
var _curent_month =  MIN('Sales'[createdon].[Date])
var _previous_month = EOMONTH( _curent_month , -2)+1
var _previous_month_value = SUMX( FILTER( ALL('Sales')  , 'Sales'[createdon].[Date]>= _previous_month && 'Sales'[createdon].[Date] < _curent_month) , [Value])
var _cur_date = MAX('Sales'[createdon].[Date])
var _previous_date = SUMX( FILTER( ALL('Sales') , 'Sales'[createdon].[Date]=_cur_date -1) , [Value])
return
SWITCH(TRUE(),
_year_slicer+_month_slicer+_day_slicer=0 , "please select one slicer!",
_year_slicer=0 && _month_slicer =1 , "please select year slicer!",
_day_slicer =1 && _month_slicer=0 , "pleaser select month slicer!",
_year_slicer =1 && _month_slicer+_day_slicer=0 , _previous_year+0,
_year_slicer+_month_slicer=2 && _day_slicer=0 ,_previous_month_value+0,
_previous_date+0

)

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly