Forum Discussion
Super UserGetting the previous month non-additive figure
Hello Power BI Community,
ROU assets (Initial Recording) (USD) :=
VAR MaxDate =
MAX ( Calendar_Month[Month end date] )
RETURN
CALCULATE (
SUM ( Lease[ROU assets (Initial recording)] ),
KEEPFILTERS ( 'Calendar_Month'[Month end date] = MaxDate ),
KEEPFILTERS ( Lease[Yearfrac] > 1 )
)
I'd like to write a measure tweaking the measure above to show the previous month lease asset balance. I've written for the eomonth formula with -1, but it didn't produce the correct number.
ROU assets (Initial Recording) (USD) (Previous month) :=
VAR MaxDate =
MAX ( Calendar_Month[Month end date] )
RETURN
CALCULATE (
SUM ( Lease[ROU assets (Initial recording)] ),
KEEPFILTERS ( 'Calendar_Month'[Month end date] = EOMONTH ( MaxDate, -1 ) ),
KEEPFILTERS ( Lease[Yearfrac] > 1 )
)
I'd like to visualize the 1st and the 2nd measure side by side in Excel to see the newly added and removed lease assets. Please could you fix the 2nd measure so that the output is shown for the previous month of the slicer selection? At the moment, 1st measure shows the intended output, but the 2nd measure shows blank result instead of the previous month figures.
Thank you for your help!
HI DataNinja777,
You can try to use the following measure formula if it suitable for your requirement:
ABC pMonth = VAR MaxDate = MAX ( 'Calendar'[Date] ) VAR pMonth = CALCULATE ( SUM ( Lease[Amortized amount] ), 'Calendar'[Date] = EOMONTH ( MaxDate, -1 ) ) RETURN IF ( [Asset Balance (Closing)] <> BLANK (), pMonth )Regards,
Xiaoxin Sheng
5 Replies
- AmiraBedh
Can you share some data ?
- DataNinja777
Anonymous , AmiraBedh
Thanks for your messages. I've uploaded the sample file which shows the current month and previous month amortization measure and the issue of previous month measure showing the blank value. In the original file, due to the lease amortization schedule only having month granurality information, I have created the time dimension table with month granurality but the attached file with the calendar table also is showing the blank output for the previous month output. Thanks for your help.
HI DataNinja777,
You can try to use the following measure formula if it suitable for your requirement:
ABC pMonth = VAR MaxDate = MAX ( 'Calendar'[Date] ) VAR pMonth = CALCULATE ( SUM ( Lease[Amortized amount] ), 'Calendar'[Date] = EOMONTH ( MaxDate, -1 ) ) RETURN IF ( [Asset Balance (Closing)] <> BLANK (), pMonth )Regards,
Xiaoxin Sheng
Hi DataNinja777 ,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
