Forum Discussion
Last N month based slicer selection
I need to show the prior 3-month data count when selecting a date in the slicer. Created date table and related with txn table.
I can get the expected result if pass the slicer selected value as a measure to calc measure. However, I've to disconnect the relationship b/w date and Txn table.
select slicer= apr
measure=selected slicer
dax_calc = calculate(distinctcount(),filter(txn,txndate>=measure-3 && txndate<measure)
since date table is common slicer for multiple charts and relationship with Txn table. Is there possible to get the above logic using dax calc without creating another standalone date table(without relationship) ?
with the relationship, it sticks to each row and unable to get the prior month
Eg.,
slicer - apr
id,dt,st
1,jan,high
2,feb,low
3,mar,low
4,apr,medium
result
high, 1
low, 2
Thank you.
Hi aj1107,
You can refer to below steps to build a last N month slicer.
1. Add column to convert dt to date.
Date = DATEVALUE([dt]&"/2017")
2. Use above date column to create a slicer table.
Selector Table2 = ADDCOLUMNS(VALUES(Test[Date]),"Month",FORMAT([Date],"mmmm"))
3. Add measure to get selected date.
Select = IF(HASONEVALUE('Selector Table2'[Date]),VALUES('Selector Table2'[Date]),BLANK())4. Write measure to count id and and filter records.
Count ID = IF(MAX([Date])<[select],COUNT(Test[id]),BLANK())
5. Create visuals.
Regards,
Xiaoxin Sheng
4 Replies
Hi aj1107,
You can refer to below steps to build a last N month slicer.
1. Add column to convert dt to date.
Date = DATEVALUE([dt]&"/2017")
2. Use above date column to create a slicer table.
Selector Table2 = ADDCOLUMNS(VALUES(Test[Date]),"Month",FORMAT([Date],"mmmm"))
3. Add measure to get selected date.
Select = IF(HASONEVALUE('Selector Table2'[Date]),VALUES('Selector Table2'[Date]),BLANK())4. Write measure to count id and and filter records.
Count ID = IF(MAX([Date])<[select],COUNT(Test[id]),BLANK())
5. Create visuals.
Regards,
Xiaoxin Sheng
Thanks for sharing the steps.
Quick question, I already have a date dimension related to test table. Per the given steps I need to create another date slicer table without a relationship.
existing date slicer is referred to multiple charts within a page. now i need to have another slicer for the last N month chart.
Is that possible to use the existing date dimension (Related->test) without another slicer table. By selecting any single date evaluate using dax while retaining the relationship.
Hi aj1107,
I think you can use the current calendar table as the source of slicer, but it will count through all similar month records, so I think you should add a year filter.
Regards,
Xiaoxin Sheng
