YoY Quick Measure: How to get for only selected months instead of Jan-Dec?

[Anonymous]

Hello,

I am using a quick measure to look at the previous years % change but the date it's looking at is from Jan-Dec when what I'm trying to get is only for the months of March-Feb.

Here is the current DAX:

PROJVOL YoY%=
IF(
ISFILTERED('data (3)'[period1]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI- provided date hierarchy or primary date column.")
VAR __PREV_YEAR =
CALCULATE(
SUM('data (3)'[PROVJOL]),
DATEADD('data (3)'[period1].[Date]. -1, YEAR)
)
RETURN
DIVIDE(SUM('data (3)'[PROVJOL]) - __PREV_YEAR, __PREV_YEAR)
)

Any help would be greatly appreciated, thank you.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please try the following formula:

PROJVOL YoY% = 
VAR __PREV_YEAR =
    CALCULATE (
        SUM ( 'data (3)'[PROVJOL] ),
        DATEADD ( PREVIOUSYEAR ( DATESYTD ( 'data (3)'[period1] ) ), 2, MONTH )
    )
RETURN
    __PREV_YEAR

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @Anonymous 
Not sure if it works without a aproper date table but you may try

PROJVOL YoY% =
VAR PreviousYearAmount =
    CALCULATE (
        SUM ( 'data (3)'[PROVJOL] ),
        SAMEPERIODLASTYEAR ( 'data (3)'[period1].[Date] )
    )
RETURN
    DIVIDE ( SUM ( 'data (3)'[PROVJOL] ) - PreviousYearAmount, PreviousYearAmount )

Other wise you can create a date table > New Table >

Date =
CALENDAR ( MIN ( 'data (3)'[period1] ), MAX ( 'data (3)'[period1] ) )

Then add the month column > New Column >

Month Name =
FORMAT ( DATE ( 1, MONTH ( 'Date'[Date] ), 1 ), "mmm" )

Create the rellationship between the two tables.

Then create the measure using the same code but utilizing the date table

PROJVOL YoY% =
VAR PreviousYearAmount =
    CALCULATE ( SUM ( 'data (3)'[PROVJOL] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    DIVIDE ( SUM ( 'data (3)'[PROVJOL] ) - PreviousYearAmount, PreviousYearAmount )

In the report, slice by the month name column from the date table.

[amitchandak]

@Anonymous , You should always use date table for time intelligence and void use of .date

PROJVOL YoY%=
IF(
ISFILTERED('data (3)'[period1]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI- provided date hierarchy or primary date column.")
VAR __PREV_YEAR =
CALCULATE(
SUM('data (3)'[PROVJOL]),
DATEADD('Date'[Date], -1, YEAR)
)
RETURN
DIVIDE(SUM('data (3)'[PROVJOL]) - __PREV_YEAR, __PREV_YEAR)
)

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

example

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

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

[Anonymous]

Hello, thank you for your response. Yes I realized just now that I should have omitted the .Date portion, but would you know how to filter it only from March-Feb instead of Jan-Dec? Or would a slicer/filter be a better approach?