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
MarcUrdang
Post Patron
Post Patron

Last month measure versus same month last year measure .. difference & % difference

Hi there .. I am a new PBI user and learning the ropes. Can you advise how to manage the following: link below

 

https://drive.google.com/open?id=19egocOe4BDzkAXOaoX-moUvTzBeaUeug

 

I have a created a number of visuals. Two of them show a measure for last month (4376) and for the same month last year (3870). I created these two cards by just using a filter to get the correct number. However now I would like to show the difference between the two and the % up or down.

 

I know that there might be a few ways to get the answer but looking for the most efficient.

 

Thanks so much,

Marc

12 REPLIES 12
MarcUrdang
Post Patron
Post Patron

ne

Hi there .. I am a new PBI user and learning the ropes. Can you advise how to manage the following: link below

 

https://drive.google.com/open?id=19egocOe4BDzkAXOaoX-moUvTzBeaUeug

 

I have a created a number of visuals. Two of them show a measure for last month (4376) and for the same month last year (3870). I created these two cards by just using a filter to get the correct number. However now I would like to show the difference between the two and the % up or down.

 

I know that there might be a few ways to get the answer but looking for the most efficient.

 

Thanks so much,

Marc

Hi .. unfortunately its still not correct .. I need to be able to speak/skype someone and talk it through ... I will try and find someone that can do this with me by reposting. 

 

thanks for all your help thus far.

 

regards,
Marc

ToffeeMark
Frequent Visitor

Just did something similar:
 
New Measure:
Current Month Sales = CALCULATE(Sum('Your Sales File'[VALUES]),LASTDATE('Your Sales File'[Date]))
which makes measure = [Current Month Sales]
then, New Measure again:
LY Current Month Sales = CALCULATE([Current Month Sales],SAMEPERIODLASTYEAR('Your Sales File'[Date]))
Let me know if it works for you.
 

Var (Growth) would just be subtracting last years from this year.

% Var (Growth) would be Divide VAR by Last years...e.g. DIVIDE([VAR],[LY Current Month Sales])

 

Good evening ...

 

I appreciate your help but the calcs are not doing what I would expect.

 

Can you build them into my workbook so that I can see what you are seeing?

 

that might help .. 

@MarcUrdang 

Please find the file https://www.dropbox.com/s/mjt6vscpogxm46i/Carrier%20Scorecard%20TESTING%20fom%20JONAS.pbix?dl=0

 

I created the calendar. And created both formulae. The current month is matching; for -13, there is some diff. Please download the data and check for any filter I missed.

amitchandak
Super User
Super User

I think it is better if you create the formulas like these

 

Year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date Filer]))
Last Year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH)))

 

 

And then you can calculate change and Change % easily.

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
Thanks.

Hi .. thanks for the response.

 

However I don't want YTDs ... I need last month and that month last year comparsion .......

 

Any thoughts?

 

thanks

Marc

You can use the same one as that is MTD. Use End of the month make sure complete month

 

Last Year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-12,MONTH))))

Use -1 or -13 as per need.

 

Hi Amichandak .. I created a card with this calc but the card shows ERROR .. is there anyway you would be able to Skype with me on this ... I am obviously missing something.

 

thanks

Marc

I created one like this ion the file you shared

 

 

Last Year MTD Sales = CALCULATE(SUM('Consol INBOUND'[TEU]),DATESYTD(dateadd('Consol INBOUND'[FLL ATD ETD],-12,MONTH)))

 

 

Other filter you have to apply. Also please make a date calender and join it with the date. That is the best way to use the date functions

Refer

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

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.