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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kr1s
Frequent Visitor

Reporting last month, YTD, and the same for last year

Hi,

 

I'm new to Power BI and DAX and I'm sure this has been asked before but I can't find a suitable solution...

 

I need to report sales quantities for the previous month, same period last year, a delta between them, and then again for YTD (up to last month) and the same for the previous year again with a delta. Below is an example of what i need:

 

 
Product2019 Month2020 MonthDelta2019 YTD2020 YTDDelta
Product A15020033%30040025%
Product B200150-33%400300-25%
 

 

My dataset contains all of the information in one table (both years).

 

I have created a calendar table which works fine, as suggested in another post.

 

Any help is greatly appreciated.

 

Thanks,

 

Kristian

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Kr1s 

 

For any time intelligence function, you could implement a custom DAX formula.

 

Community Support Team _ Sam Zha
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

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@Kr1s 

 

For any time intelligence function, you could implement a custom DAX formula.

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vivran22
Community Champion
Community Champion

Hello @Kr1s 

 

Assuming you have a data structure like this:

 

and you have created a calendar table and created a relationship on Date (One-to-Many)

 

So, you may use measures like this:

Total Orders = SUM(dtTable[Order Quantity])

Previous Month Order = CALCULATE([Total Orders],PREVIOUSMONTH('Calendar'[Date]))

YTD Orders = CALCULATE([Total Orders],DATESYTD('Calendar'[Date))

PY Orders = CALCULATE([Total Orders],SAMEPERIODLASTYEAR('Calendar'[Date]))


Calculating Delta 

Current & previous month = [Total Orders] - [Prevoious Month Order]

 

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

Kr1s
Frequent Visitor

Hi @vivran22 

 

Thanks for the quick reply.I have tried your solution but it isn't quite what I need.

My dataset contains all sales for the year so far but I need it to only display the sales figures for the previous month, i.e. if i run the report today it should show me January's figures. This should only change when the month changes.

The same goes for the YTD calculation - it should only show me data up to the end of January.

 

Thanks,

 

Kristian

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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