Dax Measure - Rolling count of table based on related selected slicer value

[Anonymous]

Hi Power BI Wizards.

I have two tables joined By Purchase Date & Date;

1. Products
2. Date Dimension

Sample Data below

I want to create a measure which will count the number of products purchased within the slected YearWeek(From Date Dimension) in the slicer, and previous 3 selected weeks of products purchased. 

Example below shows week 30 and the previous 3 weeks of products by YearWeek in the graph.

YearWeek concatenates the Year & Week Number, I guessed this would be the right approach because it takes into account the year, and a dax formula could be done easily with a numerical expression.

Thanks ahead for the help on this.

[Anonymous]

Hi @Anonymous 

Can you provide your pbix file ? This will help me better find the cause of the problem .

Best Regards

Community Support Team _ Ailsa Tao

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

[Anonymous]

Hi @Anonymous ,

I have found what the issue was. It was that your workbook had no relationship between the two tables; Products and Date Dimension.  The measure does not work with a relationship between the two.

See attached worksbooks one with and one without.  Ideally, i would not like to unecessarily create a duplicated date table wthout a relationship to get this expected outout.  Any ideas on a more defined solution ?

How do I share my workbook @Anonymous ? Upload ideally.

Thanks

[Anonymous]

Hi @Anonymous ,

Have you any update ? I need to have an active relationship on the selected filter as it dynamically filters othere charts.  

Thanks,

Barry.

[Anonymous]

Hi @Anonymous ,

Apolgies, I cannot due to the data used. I'll try recreate the workspace with dummy data..

[Anonymous]

Hi @Anonymous 

That is an error. Please take this measure as the standard .

Measure = IF(SELECTEDVALUE(Products[YearWeek in product])>=SELECTEDVALUE('Date Dimension'[YearWeek])-3 && SELECTEDVALUE(Products[YearWeek in product])<=SELECTEDVALUE('Date Dimension'[YearWeek]),1,0)

Best Regards

Community Support Team _ Ailsa Tao

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

[Anonymous]

Hi @Anonymous ,

Done, but still the issue persists with the measure. Let me show you how I am getting a different output by comparing.

This is your measure ouptut, on the left is as expected. On the right where intereaction is disabled all values=0, which is fine.

This is my output, on the left shows only the selected week and on the right all YearWeek values incorrectly= 1 

My Measure;

# Measure = IF(SELECTEDVALUE(Products[Year Week Helper])>=SELECTEDVALUE('Date Dimension'[YearWeek])-1
&& SELECTEDVALUE('Date Dimension'[YearWeek])<=SELECTEDVALUE(Products[Year Week Helper]),1,0)

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[Anonymous]

@Ashish_Mathur Looks like the right approach, how do i download the workbook? Thanks

[Ashish_Mathur]

The download link is in that article itself.

[Anonymous]

Hi @Anonymous 

In my pbix file , you can see the measure is created based on 'Date Dimension'[YearWeek] . When the Products[YearWeek in product] in duration of 'Date Dimension'[YearWeek] ,the value for measure is 1 and the visual filter will not filter to 1 .

So if the "Year Week Helper" column in your dataset display incorrect Week Number , it will cause the value of measure return 0 and then the visual filter will filter it . After all, this view only keeps data with a value of 1.

Best Regards

Community Support Team _ Ailsa Tao

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

[Anonymous]

Hi @Anonymous , The YearWeek Column is correct.  As I said the question is does the creation of this column(my way through Power Direct) have an affect on the output of the measure. Its syntax may differ I think because I don't FORMAT a date value like yourself to retreive the YearWeek Column in Products (As below screenshot), but I create a text value(In Power Direct) instead.

I don't understand how this underlined syntax is used, because it is a date only, not a YearWeek formatted value. Can you please clarify?

Thank you very much,

Barry

[Anonymous]

Hi @Anonymous 

I create two tables(product table and date table) through your description .

(1)Create a column to return YearWeek in product table .

YearWeek in product = FORMAT(Products[Purchase Date],"YYYYWW")

(2)Create a measure to judge the Products[YearWeek in product] whether in last 4 week in 'Date Dimension'[YearWeek] . If it is in this range, return 1, otherwise 0 .

Measure = IF(SELECTEDVALUE(Products[YearWeek in product])>=SELECTEDVALUE('Date Dimension'[YearWeek])-3 && SELECTEDVALUE(Products[YearWeek in product])<=SELECTEDVALUE('Date Dimension'[YearWeek]),1,0)

(3)Add a slicer with field 'Date Dimension'[YearWeek].

(4)Add a clustered column chart and put the measure in visual filter ,and set the filter is equal to 1 .Then you can use the slicer to filter the clustered column chart .

The result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[Anonymous]

Hi @Anonymous , Thank you very much for your reply.

I followed all the steps and used the workspace provided.

Unfortuantly, I am getting not getting any products in the chart using the measure you provided;

Measure = IF(SELECTEDVALUE(Products[Year Week Helper])>=SELECTEDVALUE('Date Dimension'[YearWeek])-3
&& SELECTEDVALUE(Products[Purchase_Date])=SELECTEDVALUE('Date Dimension'[YearWeek]),1,0)

I created the "Year Week Helper" column within Power Direct because it was returning the incorrect Week Number(Ie It was not alligned with ISO). Would this method affect the use of the measure you provided ? Its the only difference between the workspaces that I can see.

Thanks for the help,

Barry.

[amitchandak]

@Anonymous , fi you select a value and want to display more than that, you also need an independent date table

refer

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[Anonymous]

@amitchandak It is an independent date table. Above is a sample.