cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Community Support Team
Community Support Team

Re: Measure to visualise total asset balance of the most recent full month vs end of last previous y

HI @CUR,


Please share some sample data or pbix file so that we can test on it.

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
CUR Frequent Visitor
Frequent Visitor

Re: Measure to visualise total asset balance of the most recent full month vs end of last previous y

Hi @v-shex-msft, thanks very much for being very patient and continue to help me on this! Please allow me to mock a pbix file and send it over to you.

Thanks again,

CUR

CUR Frequent Visitor
Frequent Visitor

Re: Measure to visualise total asset balance of the most recent full month vs end of last previous y

Hi @v-shex-msft,

 

Thanks again for helping out. Here is a link to a sample file:

 

https://1drv.ms/u/s!Ajqr2tdFbLD0gVWOuadGr8s33EHh 

 

As you will see on the file, I have used thedate filter to only capture "December" month data of previous years. What I want to do is have two filters: (1) Just December months of the previous years where there is data for December AND (2) only the month two months prior to today so as today is October 2nd, I was wanting to display August 2018 so that if for example today is November 1st, it will only display September data in addition to the December data of previous years.

 

I hope this is clear.

 

Many thanks,

CUR

CUR Frequent Visitor
Frequent Visitor

Re: Measure to visualise total asset balance of the most recent full month vs end of last previous y

Community Support Team
Community Support Team

Re: Measure to visualise total asset balance of the most recent full month vs end of last previous y

Hi @CUR,

 

You can try to use below measure:

Measure =
VAR currDate =
    MAX ( 'Assets by Year and Type'[Date] )
RETURN
    IF (
        MONTH ( currDate ) = 12
            || FORMAT ( currDate, "mm/yyyy" )
                = FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 2, 1 ), "mm/yyyy" ),
        CALCULATE (
            SUM ( 'Assets by Year and Type'[Assets running total in ValDate] ),
            FILTER (
                ALLSELECTED ( 'Assets by Year and Type' ),
                FORMAT ( [Date], "mm/yyyy" ) = FORMAT ( currDate, "mm/yyyy" )
            ),
            VALUES ( 'Assets by Year and Type'[Type] )
        )
            + 0
    )

 10.PNG

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
CUR Frequent Visitor
Frequent Visitor

Re: Measure to visualise total asset balance of the most recent full month vs end of last previous y

Many many thanks @v-shex-msft! This worked!!!! Perfect! 

 

I very much appreciate your patience and help here! I can't thank you enough!

CUR Frequent Visitor
Frequent Visitor

Re: Measure to visualise total asset balance of the most recent full month vs end of last previous y

Hi @v-shex-msft,

 

Me again! May I ask if it is poossible to have say both December, March, June and September displayed also? So in the same sample file display at quarter-end numbers?

 

I was thinking of something like in the measure where this is included: "MONTH ( currDate ) = 12", somehow insert 3, 6 ,9 and 12?

 

Thanks again for your help,

CUR