Forum Discussion

mn75224's avatar
mn75224
Frequent Visitor
3 years ago

Percent Change Measure not working

Hi everyone I am trying to calculate the percent change of two values ('Previous Month' vs 'Actual'). I have 5 months of data (Jan-May) but the measure I created seems to not recognize that the most recent months data could be comparing April to May and it keeps comparing January to May values... The way I created this was Earliest event v Latest Event and the percent change formula is at the end. 

 

Earliest =
    SWITCH (
        TRUE (),
        CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "January") <> BLANK(), CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "January") ,
        CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "February") <> BLANK(), CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "February"),
        CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "March") <>  BLANK(), CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "March"),
        CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "April") <>  BLANK(), CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "April"),
CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "May") <>  BLANK(), CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "May")
    )
 
Latest =
    SWITCH (
        TRUE (),
        CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "May") <> BLANK(), CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "May") ,
        CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "April") <> BLANK(), CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "April") ,
        CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "March") <> BLANK(), CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "March"),
        CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "February") <>  BLANK(), CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "Fenruary"),
        CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "January") <>  BLANK(), CALCULATE(SUM(sheet1[TIC Amount]),'Sheet1'[Event] = "January")
    )
 
PercentChangeTIC = IF(OR([Earliest] = 0, OR([Latest] = 0, [EarliestEvent] = [LatestEvent])), BLANK(), (([Latest] - [Earliest])/(([Latest] + [Earliest]) / 2)))
Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    mn75224 , Create a date from month

     

    Date = datevalue("01-" & [Event] &"-2023")

     

    Now you can join this with a date table

    Date == Addcolumns(calendar(date(2012,01,01), date(2024,12,31) ), "Month no" , month([date])
    , "Year", year([date])
    , "Month Year", format([date],"mmm-yyyy")
    , "Month year sort", year([date])*100 + month([date])
    , "Qtr Year", format([date],"yyyy-\QQ")
    , "Qtr", quarter([date])
    , "Month",FORMAT([Date],"mmmm")
    , "Month sort", month([DAte])
    , "FY Year", if( Month(([DAte])) <7 , year(([DAte]))-1 ,year(([DAte])))
    , "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
    ,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
    , "Month Type", Switch( True(),
    eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
    eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
    Format([Date],"MMM-YYYY") )
    ,"Year Type" , Switch( True(),
    year([Date])= year(Today()),"This Year" ,
    year([Date])= year(Today())-1,"Last Year" ,
    Format([Date],"YYYY")
    )
    )

     

     

    You can use Time intelligence 

     

    example measures

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))


    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))


    last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))


    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))


    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

    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.

     

    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

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s