Switch between Discrete & YTD

Hi rajibmahmud,

 

Regarding the sample data you provided, we need to Unpivot columns "Jan","Feb","Mar" to one column like below in Query Editor. 

 

 

Then create a column to return month value:

 

MonthNum = SWITCH('Table1'[Month],"Jan",1,"Feb",2,"Mar",3)

 

Create a new table which store two values "Discrete" and "YTD". Then create a measure:

 

FilteredBySlicer = IF(LASTNONBLANK('Table2'[Column1],"")="Discrete",SUM(Table1[Value]),IF(LASTNONBLANK('Table2'[Column1],"")="YTD",CALCULATE(SUM(Table1[Value]),FILTER(ALL('Table1'),'Table1'[Category]=MAX('Table1'[Category]) && 'Table1'[MonthNum]<=MAX('Table1'[MonthNum]))),0))

 

For details, see attchached pbix file. 

 

Best Regards,
Qiuyun Yu 

 

 

Hi rajibmahmud,

 

Nope. Please go through detail information about LASTNONBLANK Function (DAX) and HASONEVALUE Function (DAX). 

 

Best Regards,
Qiuyun Yu