cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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

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.

18 REPLIES 18
Community Support

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]),
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
Not applicable

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

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
Not applicable

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

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
Not applicable
Anonymous
Not applicable
Community Support

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
Not applicable

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

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
Not applicable

Thank you Winniz,

Could you also help me the same with Previous Month.

Neelofar.

Community Support

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
Not applicable

Thanks @v-kkf-msft ,

you are a great help.

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

Anonymous
Not applicable

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:

Thanks,

Neelofar

Anonymous
Not applicable

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

Super User

@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
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-Last-Week/ba-p/1051123
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
Not applicable

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors