Last N month based slicer selection

[aj1107]

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.

[v-shex-msft · Accepted solution]

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

[aj1107]

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.

[v-shex-msft]

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

[Anonymous]

Hi ,
I have a pretty much similar issue where i need to show last 6 months data based on the selection of the slicer which is basically a month slicer(Jan,Feb) .

In my model i do have one date dimension on which multiple measures has been calculated so i can't have other date dimension 

We were able to achieve it by providing a slicer of date which is basically coming from a different table which has been created apart from the date dimensiion.

Can you please guide me through what could be necessary steps to be taken to achieve this scenerio ?

Any help would be really appreciated. 

Regards
Prabin Nepak