Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Monu_G
Regular Visitor

Showing last 6 months(not from today but in history) data based on slicer in bar chart

Hi,

 

I have a challenge in which i have to show data in bar chart visual, in which calculation are on data in history based on selected slicer, also the axis on bar chart should appear as per the month/year selected in slicer e.g.

 

Suppose today is may 28, but i want calcualte and visual data in bar chart for last 6 starting from January 2019.

 

Example, I want to visual data from july 2018 till jan 2019, if the slicer is slected for jan 2019.

 

Also, Axis should show all six months name in bar chart, and it like this it should appear dynamically everytime(from july till jan in this case)

1 ACCEPTED SOLUTION
TeigeGao
Solution Sage
Solution Sage

Hi @Monu_G ,

In your scenario, we can use the following method:

First, we need to create a calendar table using the following DAX query:

calendar = CALENDAR(DATE(2018,1,1),DATE(2019,12,31))

Then add the following calculated column:

month/year = 'calendar'[Date].[Month] & " " & YEAR('calendar'[Date])
yearmonth = YEAR('calendar'[Date])&IF(MONTH('calendar'[Date]) <10,"0"&MONTH('calendar'[Date]),MONTH('calendar'[Date]))
order = CALCULATE(DISTINCTCOUNT('calendar'[yearmonth]),FILTER(ALL('calendar'),'calendar'[yearmonth] < EARLIER('calendar'[yearmonth]))) +1
The result will like below:
PBIDesktop_0FhVGH1HPD.png

After that, we can create a measure using the following query:

Measure =
VAR mini =
    CALCULATE (
        MIN ( 'calendar'[Date] ),
        FILTER (
            ALL ( 'calendar' ),
            'calendar'[order]
                = LOOKUPVALUE (
                    'calendar'[order],
                    'calendar'[month/year], SELECTEDVALUE ( 'calendar'[month/year] )
                ) - 5
        )
    )
VAR maxaa =
    CALCULATE (
        MAX ( 'calendar'[Date] ),
        FILTER (
            ALL ( 'calendar' ),
            'calendar'[order]
                = LOOKUPVALUE (
                    'calendar'[order],
                    'calendar'[month/year], SELECTEDVALUE ( 'calendar'[month/year] )
                )
        )
    )
RETURN
    CALCULATE (
        SUM ( data[Value] ),
        FILTER ( data, data[Date] >= mini && data[Date] <= maxaa )
    )

Then drag the calendar column yearmonth as the slicer, the measure and data column date to chart:

The result will like below:

DXLsuTFhEC.gif

Here is the pbix file, please refer to it: https://1drv.ms/u/s!Ao9Of0JgO6MU8SAqL8xL0CspEtdq

Best Regards,

Teige

View solution in original post

2 REPLIES 2
TeigeGao
Solution Sage
Solution Sage

Hi @Monu_G ,

In your scenario, we can use the following method:

First, we need to create a calendar table using the following DAX query:

calendar = CALENDAR(DATE(2018,1,1),DATE(2019,12,31))

Then add the following calculated column:

month/year = 'calendar'[Date].[Month] & " " & YEAR('calendar'[Date])
yearmonth = YEAR('calendar'[Date])&IF(MONTH('calendar'[Date]) <10,"0"&MONTH('calendar'[Date]),MONTH('calendar'[Date]))
order = CALCULATE(DISTINCTCOUNT('calendar'[yearmonth]),FILTER(ALL('calendar'),'calendar'[yearmonth] < EARLIER('calendar'[yearmonth]))) +1
The result will like below:
PBIDesktop_0FhVGH1HPD.png

After that, we can create a measure using the following query:

Measure =
VAR mini =
    CALCULATE (
        MIN ( 'calendar'[Date] ),
        FILTER (
            ALL ( 'calendar' ),
            'calendar'[order]
                = LOOKUPVALUE (
                    'calendar'[order],
                    'calendar'[month/year], SELECTEDVALUE ( 'calendar'[month/year] )
                ) - 5
        )
    )
VAR maxaa =
    CALCULATE (
        MAX ( 'calendar'[Date] ),
        FILTER (
            ALL ( 'calendar' ),
            'calendar'[order]
                = LOOKUPVALUE (
                    'calendar'[order],
                    'calendar'[month/year], SELECTEDVALUE ( 'calendar'[month/year] )
                )
        )
    )
RETURN
    CALCULATE (
        SUM ( data[Value] ),
        FILTER ( data, data[Date] >= mini && data[Date] <= maxaa )
    )

Then drag the calendar column yearmonth as the slicer, the measure and data column date to chart:

The result will like below:

DXLsuTFhEC.gif

Here is the pbix file, please refer to it: https://1drv.ms/u/s!Ao9Of0JgO6MU8SAqL8xL0CspEtdq

Best Regards,

Teige

Hi Tiege,

 

Thank you, so much for your solution, it works fine in normal condition, however in my case, the SSAS cube model in created on Visual Studio 2015 and SQL server 2016, which does not support selected value and i cant do full import in PowerBI as it crashes, IF(HASONEVALUE) might work but not sure how to use it with your solution, Is there anything you can suggest which keeping in mind my limitations.

 

Thanks,

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.