cancel
Showing results for
Did you mean:
Post Partisan

## DAX for Previous calculations

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

Neelofar

1 ACCEPTED SOLUTION
Community Support

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-oT4S...

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.

18 REPLIES 18
Community Support

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]),
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.

Post Partisan

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

Community Support

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/EdW5GDvVEKZCpkJUTaol72...

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.

Post Partisan

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=...

Community Support

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

Post Partisan
Post Partisan
Community Support

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-oT4S...

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.

Post Partisan

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.

Community Support

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

Post Partisan

Thank you Winniz,

Could you also help me the same with Previous Month.

Neelofar.

Community Support

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

Post Partisan

Thanks @v-kkf-msft ,

you are a great help.

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

Post Partisan

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

Post Partisan

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

Super User

@neelofarshama , 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
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
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-La...
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
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-Powe...

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.

Post Partisan

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

Super User

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

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors