cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CUR Frequent Visitor
Frequent Visitor

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

Hi,

 

I'm really new to Power Bi and not very good at DAX yet so I was hoping someone might be able to help me.

 

I'm trying to create a visualisation of total asset balance in the most recent full month (e.g. August 2018) dynamically (so if today is October, I will have a bar for September 2018 rather than August 2018) vs end of last previous years (i.e. as at December of each year). So something like the chart attached.  

 

I was able to use the quick measure Filtered value function to only filter the sum of all assets for the month of December. So I have the previous years sorted. However, I need to sort of insert another bar for the most recent full month for this year.

 

Any ideas how I can make this work?

 

Thanks

CUR

Asset balance comparison chart.png

1 ACCEPTED SOLUTION

Accepted Solutions
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: | |
16 REPLIES 16
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 sample measure if it works for your scenario.

measure =
VAR CurrDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date]
                >= DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
                && [Date] <= currDate
        ),
        VALUES ( Table[Legend field] )
    )

 

If above not help, please share some sample data for test and coding formula, it is hard to coding formula without any sample data.

 

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

Thanks very much @v-shex-msft for the suggestion. Though it didn't work for me unforunately. Smiley Sad

 

I'd also love to share the data but I'm unable to due to privacy reasons. Smiley Sad However, I have created a sample data set and a similar visualisation that I want to replicate in Power BI. Please refer to the attached. 

 

So I have three columns:

 

1. Assets (in this example Asset A, B & C)

2. Date (it includes all month-end dates)

3. Asset values .

 

I only want to chart values as at December 31 of each year and the most recent previous two month (so as today is September 14, it should then give me a bar for July 31 also). I want to exclude all other periods. When it comes to next month so say October 5, I want the chart to display all December 31 values and August 31.

 

I have used this measure to get the December 31 values to display:

 

Measure=
CALCULATE(
SUM(Table[Assets]),
Table[Date].[Month] IN { "December" },
ALL(Table[Date].[MonthNo])
)

 

However, I need to insert the dynamic bar for this year 2018.  

 

Do you have other suggestions by any chance?

 

Thanks

CUR

 

Sample data and chart.png

 

CUR Frequent Visitor
Frequent Visitor

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

@EnterpriseDNA I was wondering if you have any suggestions with the above by any chance? I saw some of your great vids though I still can't figure out the right syntax for my issue here! Any help would be much appreciated. Thanks!

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, I was wondering if you have any more ideas on how to make this work? Thanks

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 formula if it suitable able for your requirement:

Measure =
VAR currDate =
    MAX ( 'Table'[Date] )
RETURN
    IF (
        MONTH ( currDate ) = 12
            || FORMAT ( currDate, "mm/yyyy" )
                = FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 2, 1 ), "mm/yyyy" ),
        CALCULATE ( SUM ( 'Table'[Amount] ), VALUES ( 'Table'[Assets] ) )
    )

17.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

Thanks very much @v-shex-msft

 

This sort of worked altough it has summed up all the assets for the year (i.e. from January to December) rather than just the assets at the end of the year (i.e. just December). Any ideas how I can fix this by any chance?

 

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

I should clarify - so just December for the years that passed AND just July this year, dynamically changing depending on what day is today (i.e. if it is 1 October today then it would show  just December for the years that passed AND just August this year).

 

Thanks again,

CUR

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,

 

If you mean display running total value instead current month value, you can add condition to filter on date range:

Measure =
VAR currDate =
    MAX ( 'Table'[Date] )
RETURN
    IF (
        MONTH ( currDate ) = 12
            || FORMAT ( currDate, "mm/yyyy" )
                = FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 2, 1 ), "mm/yyyy" ),
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Date] <= currDate
                    && YEAR ( [Date] ) = YEAR ( currDate )
            ),
            VALUES ( 'Table'[Assets] )
        )
    )

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

Thanks very much @v-shex-msft! Unfortunately this still didn't work. It still aggregated all the assets during the year rather than just for the month of December for years 2017, 2016, 2015, 2014, 2013...etc. It also aggregated all the assets for the whole year of 2018. 

 

Perhaps is it because of the formatting of the date hence the filter doesn't work? 

 

Thanks again for your help,

CUR