Get Previous year, Previous month and previous quarter values in same graph

[vijay8190]

Hi Team,

Requiired Previous year, Previous month and previous quarter values in same graph with Date hierarchy form.

For example:
previous year : For year 2022 values shloud be compare for 2021. 

previous month: march 2022 values should be compare for February 2022.

previous Quarter: Quarter 3 for 2022 values should be compare for Quarter 2 for 2022 values.

All three combination required in single measure for Date hierarchy.

In Date hierarchy:

• if we are on year level then previous year should be compare For year 2022 values shloud be compare for 2021 year value.
• if we are on Quarter hierarchy then Quarter 3 for 2022 values should be compare for Quarter 2 for 2022 value.
• if we are on Month hierarchy then march 2022 values should be compare for February 2022 value.

Thank you!

Spoiler

[vijay81902287]

@Anonymous Thanks for your reply. I am unable to see the correct data for Quartre and Month values should reflect like: 

previous month: for march 2022 values should be compare for February 2022.

previous Quarter: for Quarter 3 2022 values should be compare for Quarter 2 for 2022 values.

but as of now values are showing for Previous year Quarter and Previous year month.

[Anonymous]

Hi @vijay8190 ,

Please try below steps:

1. Create a New Measure: You'll need to create a new measure that dynamically calculates the value based on the current level of the Date hierarchy (Year, Quarter, Month) being viewed. This can be achieved using DAX and leveraging the 'ISINSCOPE' function to determine the current hierarchy level.

2. DAX Formula: Here is a simplified version of the DAX formula that you can use and adapt for your specific needs:

Dynamic Comparison = 
VAR IsYear = ISINSCOPE('Date'[Year])
VAR IsQuarter = ISINSCOPE('Date'[Quarter])
VAR IsMonth = ISINSCOPE('Date'[Month])
RETURN
IF(
    IsYear,
    CALCULATE(
        [YourMeasure],
        DATEADD('Date'[Date], -1, YEAR)
    ),
    IF(
        IsQuarter,
        CALCULATE(
            [YourMeasure],
            DATEADD('Date'[Date], -1, QUARTER)
        ),
        IF(
            IsMonth,
            CALCULATE(
                [YourMeasure],
                DATEADD('Date'[Date], -1, MONTH)
            ),
            BLANK()
        )
    )
)

- Replace '[YourMeasure]' with the measure you wish to compare across time periods.
- This formula checks the current level of the Date hierarchy and calculates the value for the previous year, quarter, or month accordingly.

3. Usage: Add this new measure to your graph alongside your original measure. Ensure your graph is using the Date hierarchy from your Date table to allow dynamic comparison as you drill down through the hierarchy levels.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dongare03]

Not working i want 

I want this all in one visual and when i drill down that time it's show last year vs current year, last quarter vs current quarter, last month vs current month and last day vs current day sale comparison in one line chart

[jolind1996]

Hello,

You can create a measure in Power BI that calculates the values for the previous year, previous month, and previous quarter based on the selected level of the date hierarchy. Here is an example of how you can do this:

Previous Value =
VAR CurrentLevel = SELECTEDVALUE('Date'[Hierarchy Level])
VAR PreviousValue =
    SWITCH(
        CurrentLevel,
        "Year", CALCULATE([Measure], SAMEPERIODLASTYEAR('Date'[Date])),
        "Quarter", CALCULATE([Measure], DATEADD('Date'[Date], -1, QUARTER)),
        "Month", CALCULATE([Measure], DATEADD('Date'[Date], -1, MONTH))
    )
RETURN PreviousValue

In this measure, 'Date' is the name of the date table, 'Date'[Hierarchy Level] is the column containing the selected level of the date hierarchy, and 'Date'[Date] is the column containing the dates. The measure uses the `SELECTEDVALUE` function to get the selected level of the date hierarchy, and the `SWITCH` function to calculate the value for the previous year, previous month, or previous quarter based on the selected level. The `SAMEPERIODLASTYEAR` and `DATEADD` functions are used to shift the dates to the previous period.

Please note that you will need to replace 'Date', 'Date'[Hierarchy Level], 'Date'[Date], and [Measure] with the actual names of your date table, hierarchy level column, date column, and measure. Also, make sure that the date hierarchy is correctly set up and that the 'Date'[Hierarchy Level] column contains the correct values for the selected level of the hierarchy.

I hope this helps! Let me know if you have any further questions.

Best regards,

Johannes

[Dongare03]

Not working i want 

I want this all in one visual and when i drill down that time it's show last year vs current year, last quarter vs current quarter, last month vs current month and last day vs current day