dax universal formula to calculate difference to previous period sales

[gvg]

Hi,

Is there a universal way to compare to previous period ? There are plenty of threads on how to compare to previous month or year, like this:

     sales_before = calculate(sum('Table'[Sales]), PARALLELPERIOD('Table'[Date],-1,MONTH)) 

But is it possible to have a universal formula, that works both on years, quarters, months, days depending on what filter is used? I.e. if I show in a chart Year1 and Year2, I'd like to calculate the difference between Year1 and Year2. If I double click on  Year1 to show quarters of that year, I'd like to see differences between the displayed quarters.

[vanessafvg]

@gvg are you referring to https://msdn.microsoft.com/en-us/library/ee634972.aspx  SAMEPERIODLASTYEAR?

[gvg]

No. I am referring to previous period, not parallel period. If years 2017 and 2016 are charted, I want the difference between 2017 and 2016. If 2017 is drilled down to 2017-Q1 and 2017-Q2, I want the difference between those adjacent quarters. If 2017-Q1 is drilled down to 2017-Jan, 2017-Feb and 2017-Mar, I want the difference between the adjacent months.

[v-jiascu-msft · Accepted solution]

@gvg

Hi,

There is no such a function as far as I know. Maybe there is a workaround we can try.

1. We create a hierarchy by ourselves. Every level is from a column of the Date Table.
2. Create a measure like this. I am sure you have to change a lot. It just shows the way we can do.

Measure =
VAR CurrentSA = [Sales Amount]
RETURN
    IF (
        ISFILTERED ( 'Date'[Calendar Year] ),
        CurrentSA
            - CALCULATE ( [Sales Amount], PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) ),
        IF (
            ISFILTERED ( 'Date'[Calendar Year Quarter] ),
            CurrentSA
                - CALCULATE ( [Sales Amount], PARALLELPERIOD ( 'Date'[Date], -1, QUARTER ) ),
            IF (
                ISFILTERED ( 'Date'[Calendar Year Month] ),
                CurrentSA
                    - CALCULATE ( [Sales Amount], PARALLELPERIOD ( 'Date'[Date], -1, MONTH ) ),
                IF (
                    ISFILTERED ( 'Date'[Date] ),
                    CurrentSA
                        - CALCULATE ( [Sales Amount], 'Date'[Date] - 1 )
                )
            )
        )
)

Note: 1. No filter can be applied.

          2. Use the Preview Matrix.

Best Regards!

Dale

[Chandrra]

Could you please share the PBIX file ?

[mdaamirkhan]

I use below query but value is not changing for different QTR in Measures. it showing same as it is in USD_MNF

Measure =
VAR CurrentSA = [Sales Amount]
RETURN
IF (
ISFILTERED ( PADDS[Year] ),
CurrentSA
- CALCULATE ( [Sales Amount], PARALLELPERIOD ( PADDS[PERIOD], -1, YEAR ) ),
IF (
ISFILTERED ( PADDS[Quater] ),
CurrentSA
- CALCULATE ( [Sales Amount], PARALLELPERIOD ( PADDS[PERIOD], -1, QUARTER ) ),
IF (
ISFILTERED ( PADDS[Month] ),
CurrentSA
- CALCULATE ( [Sales Amount], PARALLELPERIOD ( PADDS[PERIOD], -1, MONTH ) ),
IF (
ISFILTERED ( PADDS[PERIOD] ),
CurrentSA
- CALCULATE ( [Sales Amount], PADDS[PERIOD] - 1 )
)
)
)
)

[Anonymous]

Same here.. 😞

[OwenAuger]

Just throwing in another solution from an earlier thread, on essentially the same question:

Similar logic to @v-jiascu-msft 's answer above but based on day count of filtered period vs parallel period of a given type.

https://community.powerbi.com/t5/Desktop/COMPARISON-Current-Period-sum-Vs-Previous-Period-sum-Change...

[gvg]

@v-jiascu-msft  thanks!