Switch between Discrete & YTD

[rajibmahmud]

Hi,

I have below data in system which is in Discrete month.

	Jan	Feb	Mar
Sales	10	11	14
Profit	5	6	6

My requirement is to create a slicer of Discrete & YTD and switch between Discrete and YTD data based on that.

[v-qiuyu-msft · Accepted solution]

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 

[v-qiuyu-msft · Accepted solution]

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 

[rajibmahmud]

Thanks alot.

I never used LASTNONBLANK before, can it be replaced with Hasonevalue?

[v-qiuyu-msft · Accepted solution]

Hi @rajibmahmud,

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

Best Regards,
Qiuyun Yu