How to construct a weekly change measure when the weeks are inconsistent?

[Anonymous]

Hi Everyone

I want to include measures in my report that show week on week changes in my data - however I have a complication in that the data is provided to me at inconsistent time intervals.

What I have is a field for "generation date" which shows the refresh date of the dataset. So for example, the data provided on 1st May 2020 will have [Generation Date] = 2020-05-01 for every row, data provided on 15th May 2020 will have [Generation Date]=2020-05-15 for every row and so on. I append these queries in query editor so there is effectively one big running table for the data.

I have slicers that show "Generation Date" to essentially show a present snapshot of the data. What i would like to do is include measures that show how certain metrics have moved since the 2nd most recent generation date to the currently selected one.

Can anyone suggest some DAX that would allow me construct such measures? I am okay with the calculation part ie showing percentage or absolute movements, but I need some help in having this "change between generation dates" part be dynamic.

thanks in advance for any help

GC4002

[amitchandak]

@Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in a table format?

For the week on Week Comparision, you can refer

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

[Anonymous]

Hi @amitchandak 

I can't share a full data set as this is sensitive data. However the table below demonstrates what Im trying to do:

I'm trying to build a measure that will essentially calculate the difference in count of task status between dates in report generated. I envision something using variables, I have made a dax expression that counts task status based on MAX[report generated] but its the 2nd part that I struggle with. I wrote the following DAX expression:

Previous Week Tasks = max('1 - All Tasks Combined'[Report Generated])-1

However this would return 06 / 07 / 2020 - clearly the dax is just subtracting one from my MAX date (being 07/07/2020). I want a formula that would return my 2nd most recent Report Generated date, ie 30 / 06 / 2020 in my example 

[Anonymous · Accepted solution]

Hi Everyone

I have solved this myself, I hope the community can benefit from my workings.

Step 1 - RANKX

I used rankx to provide a ranking to my Report Generated dates:

Report Generated Rank = RANKX(ALLSELECTED('1 - All Tasks Combined'[Report Generated]),'1 - All Tasks Combined'[Report Generated],,ASC)-1

I had to include a -1 as there were some dummy rows of data with no date included which were given the rank of 1. The result is the 3 distinct dates in my table now are ranked correctly:

Step 2 - Using output of rankx as a filter 

The output of rankx can now be used as a filter in calculating totals for current week and for previous week:

Latest Week Tasks = CALCULATE(COUNTA('1 - All Tasks Combined'[Task Status]),FILTER('1 - All Tasks Combined',[Report Generated Rank]=max('1 - All Tasks Combined'[Report Generated Rank])))

Previous Week Tasks = CALCULATE(COUNTA('1 - All Tasks Combined'[Task Status]),FILTER('1 - All Tasks Combined',[Report Generated Rank]=max('1 - All Tasks Combined'[Report Generated Rank])-1))

You can then use simple measures to calculate the difference in absolute or relative percentage terms, as viewed on this table: