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

[Monu_G]

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)

[TeigeGao · Accepted solution]

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:

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:

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

Best Regards,

Teige

[Monu_G]

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,