DAX for Previous calculations

[Anonymous]

Hi All,

I have a report shown below a week ending date and a starts measure. For this report I need 

1)How many starts last week compared to the previous week, previous month, previous year

2)How many starts last month compared to the previous month, previous year

3)How many starts last year compared to the previous year

4)How many starts last week compared to the cumulative starts for the same period of the previous year

Please help me with these DAX calculations.

Thanks in Advance

Neelofar

[v-kkf-msft]

Hi @Anonymous ,

Based on your description, I create the following sample data. I first create a calculated column:

Worknum = WEEKNUM( 'Table'[WEEK ENDING], 2 )

 Then create the following measures:

Last Week = 
CALCULATE(
  SUM('Table'[STARTS]),
  LASTDATE( 'Table'[WEEK ENDING] )
)

 The cumulative starts for the same period of the previous year:

Same Period = 
var _worknum = 
  CALCULATE(
    MAX('Table'[Worknum]),
    DATESYTD('Table'[WEEK ENDING])
  )
var result =
  CALCULATE(
    SUM('Table'[STARTS]),
    FILTER(
      ALL('Table'), 
      WEEKNUM('Table'[WEEK ENDING],2) = _worknum 
      && 'Table'[WEEK ENDING] <> MAX('Table'[WEEK ENDING])
    )
  )
return result

Last Month = 
CALCULATE(
  SUM('Table'[STARTS]),
  DATESMTD('Table'[WEEK ENDING])
)

Last Year = 
CALCULATE(
  SUM('Table'[STARTS]),
  DATESYTD('Table'[WEEK ENDING])
)

Previous Week = 
CALCULATE(
  SUM('Table'[STARTS]),
  DATEADD(
    LASTDATE( 'Table'[WEEK ENDING] ),
    -7,
    DAY
  )
)

Previous Month = 
CALCULATE(
  SUM('Table'[STARTS]),
  PREVIOUSMONTH(
    LASTDATE('Table'[WEEK ENDING])
  )
)

Previous Year = 
CALCULATE(
  SUM('Table'[STARTS]),
  PREVIOUSYEAR('Table'[WEEK ENDING])
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

Hi Winniz,

Thank you for the solution I tried to implement the same in my report but I am getting Previous month and Previous Week as null. Also the the Starts is same as Last Week and Last month. Can you please tell me where am I going wrong

[v-kkf-msft]

Hi @Anonymous ,

That may be caused by our different models. This is my PBIX file, you can see the difference.

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-lazhang_microsoft_com/EdW5GDvVEKZCpkJUTaol72EBEOT8oPGVn65vGk2FgPjAvQ?e=980aQt

 If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

Hi Winniz,

I have seen the file you sent its may be beacuse of the date hierarchy I am using. I am also sharing my file, please suggest

https://caltimes-my.sharepoint.com/personal/neelofer_shama_latimes_com/_layouts/15/onedrive.aspx?id=...

[v-kkf-msft]

Hi @Anonymous ,

I can't access your files after clicking on the link, you need to give me access permissions. You can refer to the following document:

Share SharePoint files or folders - Office Support (microsoft.com)

Best Regards,

Winniz

[Anonymous]

sorry please try this link and let me know

https://caltimes-my.sharepoint.com/:u:/g/personal/neelofer_shama_latimes_com/ESU-JlpHdGFBiyk1Kj378yU...

[Anonymous]

https://caltimes-my.sharepoint.com/:u:/g/personal/neelofer_shama_latimes_com/ESU-JlpHdGFBiyk1Kj378yU...

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

I made some changes to your PBIX file and now we can get the correct value. This is the PBIX file.

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-lazhang_microsoft_com/EYCBrkiCeZFBpl1nq-oT4S4BILc3w-J7HPVeVNKGrt24aA?e=eEVbfB

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

Hi @v-kkf-msft ,

I implemented the same DAX as you suggested, now my user wants Previous Year DAX to show up running totals, same like you did for Last Year.

Eg: 221,457.00

     219,090.00

     216,331.00

     213,711.00

This is expected in Previous Year.

Please help

[v-kkf-msft]

Hi @Anonymous ,

Modify the measure as:

Previous Year = 
CALCULATE(
    sum(DSI_STARTS_STOPS[STARTS]),
    FILTER(
        ALL(DSI_STARTS_STOPS),
        DSI_STARTS_STOPS[Week Number] <= MAX(DSI_STARTS_STOPS[Week Number])
        && YEAR(DSI_STARTS_STOPS[WEEK_ENDING]) = YEAR(MAX(DSI_STARTS_STOPS[WEEK_ENDING]))-1
    )
)

Best Regards,
Winniz

[Anonymous]

Thank you Winniz,

Could you also help me the same with Previous Month.

Thanks in Advance,

Neelofar.

[v-kkf-msft]

Hi @Anonymous ,

I think this is inappropriate. As you can see, there are 4 rows of data in March and May, and 5 rows of data in April, so what kind of output do you want?

Best Regards,
Winniz

[Anonymous]

Thanks @v-kkf-msft , 

you are a great help.

Could you also give me the same running total for Previous Month.

[Anonymous]

Hi Winniz,

Thank you so much for the help, but there is a problem with the DAX measures when I add a filter on the visual they do not work as intended. The Previous week does not work correctly after adding filter below is attached screenshot.

Before filter:

After filter:

looking forward for your help.

Thanks,

Neelofar

[Anonymous]

I resolved this, just replaced all() with allselected() in the DAX and it worked. Thank you.

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if these blogs and video can help

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
2.Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Thanks yuou for reply, I have shared the sample data in the screenshot that is what I have.

[amitchandak]

@Anonymous , I think with help from the date table, the above blogs should help.