YoY,WoW,QoQ,MoM

[Anonymous]

hi all,

 I need your help in building a visual in such a way that I have YoY,MoM,QoQ and WoW for all the calculated measures.

Please find below for the format of the data and desired output.

Data Format:

Date	Product	Sales	Units
01/01/2019	A	51512	10
01/01/2020	A	54811	52
01/01/2021	A	15845	15

Output:

	2020	2021	YoY
Sales	54811	15845	-38966
Units	52	15	-37

The same for MoM and QoQ and WoW

Thanks in advance

[Geradav · Accepted solution]

@Anonymous 
Here is what I came up with for Quarter over Quarter

QoQ =
VAR SelectedQuarter =
    YEAR ( MIN ( TableDoD[Date] ) ) & QUARTER ( MIN ( TableDoD[Date] ) )
VAR PrevQuarter =
    YEAR ( MIN ( TableDoD[Date] ) - 1 )
        & QUARTER ( MIN ( TableDoD[Date] ) - 1 )
VAR vQTotal =
    CALCULATE (
        SUM ( TableDoD[Units] ),
        FILTER (
            ALL ( TableDoD[Date] ),
            YEAR ( TableDoD[Date] ) & QUARTER ( TableDoD[Date] ) = SelectedQuarter
        )
    )
VAR vPQTotal =
    CALCULATE (
        SUM ( TableDoD[Units] ),
        FILTER (
            ALL ( TableDoD ),
            YEAR ( TableDoD[Date] ) & QUARTER ( TableDoD[Date] ) = PrevQuarter
        )
    )
VAR Dif = vQTotal - vPQTotal
VAR Result =
    SWITCH ( TRUE (), Dif = 0, Dif, Dif > 0, "+" & Dif, Dif < 0, Dif )
RETURN
    Result

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, you only need to create year, month, quarter and week columns respectively, and then apply this measure in different scenarios.

Revenue in same period last year = CALCULATE([Revenue],SAMEPERIODLASTYEAR('Calendar'[Date]))

Variance = IF(NOT(ISBLANK([Revenue in same period last year])),[Revenue]-[Revenue in same period last year])

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Geradav]

@Anonymous 

Imagining you have only one table and no calendar date, you could try the following. To obtain the selected value I used the date column as a filter visual. The same function can be adapted for Quarter, Month and week.

YoY = 
VAR SelectedDate =
    YEAR ( MIN ( TableDoD[Date] ) )
VAR vYTD =
    CALCULATE (
        SUM ( TableDoD[Units] ),
        FILTER ( ALL ( TableDoD[Date] ), YEAR ( TableDoD[Date] ) = SelectedDate )
    )
VAR vLYTD =
    CALCULATE (
        SUM ( TableDoD[Units] ),
        FILTER ( ALL ( TableDoD ), YEAR ( TableDoD[Date] ) = SelectedDate - 1 )
    )
VAR Dif = vYTD - vLYTD
RETURN
    SWITCH ( TRUE (), Dif = 0, Dif, Dif > 0, "+" & Dif, Dif < 0, Dif )

Let us know if that works for you

David

[Geradav · Accepted solution]

@Anonymous 
Here is what I came up with for Quarter over Quarter

QoQ =
VAR SelectedQuarter =
    YEAR ( MIN ( TableDoD[Date] ) ) & QUARTER ( MIN ( TableDoD[Date] ) )
VAR PrevQuarter =
    YEAR ( MIN ( TableDoD[Date] ) - 1 )
        & QUARTER ( MIN ( TableDoD[Date] ) - 1 )
VAR vQTotal =
    CALCULATE (
        SUM ( TableDoD[Units] ),
        FILTER (
            ALL ( TableDoD[Date] ),
            YEAR ( TableDoD[Date] ) & QUARTER ( TableDoD[Date] ) = SelectedQuarter
        )
    )
VAR vPQTotal =
    CALCULATE (
        SUM ( TableDoD[Units] ),
        FILTER (
            ALL ( TableDoD ),
            YEAR ( TableDoD[Date] ) & QUARTER ( TableDoD[Date] ) = PrevQuarter
        )
    )
VAR Dif = vQTotal - vPQTotal
VAR Result =
    SWITCH ( TRUE (), Dif = 0, Dif, Dif > 0, "+" & Dif, Dif < 0, Dif )
RETURN
    Result

[amitchandak]

@Anonymous , For the output, you can use Matrix with show on row, that will move measure to row.

For Time intelligence refer my blog

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

hi @amitchandak ,

Where the Years and YoY calculation to be displayed?

[amitchandak]

@Anonymous , You will get measures like this year, last year, and YOY.

Now You have to use union and summarize to create a table of what you want. Measure to measure and dimension conversion

union(
summarize('Table',"Measure","sales","This period",[SALES YTD],"Last period",[SALES LYTD],"POP",[SALES YOY])
summarize('Table',"Measure","unit","This period",[unit YTD],"Last period",[unit LYTD],"POP",[unit YOY])
)