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
Anonymous
Not applicable

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

Hi @Anonymous,

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

16 REPLIES 16
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

 

I'd also love to share the data but I'm unable to due to privacy reasons. 😞 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

 

HI @Anonymous,

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Hi @Anonymous,

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

HI @Anonymous,


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

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Anonymous
Not applicable

Hi @Anonymous,

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Anonymous
Not applicable

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

 

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

Anonymous
Not applicable

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

Anonymous
Not applicable

@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!

Anonymous
Not applicable

Hi @v-shex-msft, I was wondering if you have any more ideas on how to make this work? Thanks

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.