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
BadenhoA
New Member

Problem trying to calculate previous month totals

I am trying to do a report on printing so we can watch the increase or decrease in printing as a percentage.

However no matter what I try the totals of socalled previous month is equivalent to to current month or depending on what I do no value at all.

 12.png

I have tried with seprate date table added. No difference. also tried dateadd and previousmonth options still no joy.

I either get no value or as you can see wrong value.

Thanks

 

 

1 ACCEPTED SOLUTION

Thanks for the hint

This folowing formula worked :

 

LM =
CALCULATE (
    SUM('Printer Data'[Total Printed Pages]) ;
    PREVIOUSMONTH( 'Printer Data'[Date]) ;
    ALLSELECTED('Printer Data')
)

 

View solution in original post

6 REPLIES 6
v-haibl-msft
Employee
Employee

@BadenhoA

 

You need to create a calendar table firstly with formula like below.

 

Calendar = 
CALENDAR ( "1/1/2016", "12/31/2017" )

And create relationship between the Calendar table with the fact table.

Problem trying to calculate previous month totals_1.jpg

 

Then we only need to create a measure with following expression.

 

LM = 
CALCULATE (
    SUM ( Table1[Total Printed Pages] ),
    PREVIOUSMONTH ( 'Calendar'[Date] )
)

Problem trying to calculate previous month totals_2.jpg

 

Best Regards,
Herbert

 

Thanks for the reply.

This is exactly what id did I have tried with external table using the internal date field.

None of the options produce the correct result no matter as I mention if I use Previousmonth, dateadd or parallelperiod pointing to either internal or external date table with the relationship set up.

For some weird reason it is not working. 

I am starting suspect that the problem might be regional formatting.

I will try a few more things before giving it up. 

I have reverted to the March 10 version of PowerBI Desktopas was suggested as well still no joy.

 

@BadenhoA

 

If possible, could you please provide your PBIX file to me? I can take a look at it.

 

Best Regards,
Herbert

I can do that no problem at all .

You can get the file from here: https://www.dropbox.com/s/eo3c1s1ww2rn37g/Old%20Test.pbix?dl=0

Thanks

 

@BadenhoA

 

Please try with following measure formula.

 

LM = 
CALCULATE (
    SUM ( 'Printer Data'[Total Printed Pages] ),
    PREVIOUSMONTH ( 'Calendar'[Date] ),
    ALLSELECTED( 'Printer Data' )
)

Best Regards,
Herbert

Thanks for the hint

This folowing formula worked :

 

LM =
CALCULATE (
    SUM('Printer Data'[Total Printed Pages]) ;
    PREVIOUSMONTH( 'Printer Data'[Date]) ;
    ALLSELECTED('Printer Data')
)

 

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.