Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
neelofarshama
Post Partisan
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

 

neelofarshama_0-1614065973958.png

Please help me with these DAX calculations.

Thanks in Advance

Neelofar

1 ACCEPTED SOLUTION

Hi @neelofarshama ,

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

image.png

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.

View solution in original post

18 REPLIES 18
v-kkf-msft
Community Support
Community Support

Hi @neelofarshama ,

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

 image.png

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.

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

neelofarshama_1-1614166129476.png

 

Hi @neelofarshama ,

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.

 

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

Hi @neelofarshama ,

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

 

Hi @neelofarshama ,

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

image.png

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.

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.

neelofarshama_0-1615402743105.png

Please help

Hi @neelofarshama ,

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

v-kkf-msft_0-1615426015726.png

Best Regards,
Winniz

Thank you Winniz,

 

Could you also help me the same with Previous Month.

 

Thanks in Advance,

Neelofar.

Hi @neelofarshama ,

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?

image.png

Best Regards,
Winniz

Thanks @v-kkf-msft , 

you are a great help.

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

neelofarshama_0-1615445010552.png

 

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:

neelofarshama_0-1614683162202.png

After filter:

neelofarshama_1-1614683203350.png

looking forward for your help.

Thanks,

Neelofar

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

amitchandak
Super User
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
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-La...
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-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.

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.