PowerBi Measures

[Anonymous]

Hello All,

I have files that contain current subscribers for each week of the past year that I've appended together. I've included a report date field to tell which report the record was from. I've calculated the total subscribers for the most recent week and am trying to calculate the total sub for the prior week. It is returning the correct total for the prior week subscribers but is not returning prior week totals for each week in my table.

This is the code I am using: 

Total Subscribers Previous Week =

VAR MostRecentWeek = MAX('Append1 - Current'[Report Date])

VAR PreviousWeek = MostRecentWeek - 7

RETURN

CALCULATE(

    DISTINCTCOUNT('Append1 - Current'[Account]),

    'Append1 - Current'[Report Date] = PreviousWeek,'Append1 - Current'[isCurrent] = "Current"

)

Anyone know what I could be doing wrong?

[NordicDrow]

Hi there!

If you have a file with columns "Date", "Prior Week Date" and "Subscribers", you could just go to PowerQuery, merge table, merge with itself, columns "Prior Week" in the first to "Date" in the second. Then just expand "Subscribers".

Hope it helps, tell me if you need any further assistance.

[audreygerred · Accepted solution]

Hi! Here is a great article that describes how to do WoW measures: Week-related calculations – DAX Patterns

Scroll until you find this section and it walks you through setting up the DAX you will need: