Forum Discussion

VamshiKrishna84's avatar
VamshiKrishna84
New Member
5 years ago

Margin Percent Last year by Date

Hello,

 

I have a requirement to get Margin % for Last year (LY) when a Date for current year is selected as shown below. I cannot use Sameperiodlast year since we go off of fiscal calendar. I got it working using DAX below. However, i can't figure out why total on Margin % LY isnt working (highlighted Yellow). It just copies the value from last cell. Have spent several hours without luck. Any help will be greatly appreciated.

 

Note - I have Date and LYDate in a Date Dimension table in the database. 

 

Margin % TY:= DIVIDE(Stats[MarginAmt TY],Stats[SaleAmt TY],1)

Margin % LY Sub:= CALCULATE(Stats[Margin % TY],FILTER(ALL('Date'),'Date'[Date] = MAX('Date'[LYDate])))

 

 

 

 

Current Year VS Last Year
DAX
Margin Percentage

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    VamshiKrishna84 , First Create a measure like

    Margin %= DIVIDE(Stats[MarginAmt],Stats[SaleAmt],1)

     

    The use time intelligence and date table to create TY and LY like these examples

    YTD Sales = CALCULATE([Margin %],DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE([Margin %],DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE([Margin %],DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE([Margin %],DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE([Margin %],DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE([Margin %],dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE([Margin %],filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE([Margin %],filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

     

    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.

     

    Please provide your feedback comments and advice for new videos
    Tutorial Series Dax Vs SQL Direct Query PBI Tips
    Appreciate your Kudos.

    • VamshiKrishna84's avatar
      VamshiKrishna84
      New Member
      5 years ago

      amitchandak  I appreciate your response. I cannot use time intelligent functions since we follow Fiscal calendat. Subtracting an year from current years date wouldnt land us on last year's fiscal date. Thats why i had to create a Date Dim table and have LY dates pre-populated. I got both SaleAmt measure working for Dates and total row using SUMX functions below.

       

       

      SaleAmt TY:= CALCULATE(SUM(Stats[SaleAmt]))
SaleAmt LY Sub:= CALCULATE(Stats[SaleAmt TY],FILTER(ALL('Date'),'Date'[Date] = MAX('Date'[LYDate])))
SaleAmt LY:= SUMX(VALUES('Date'[Date]),Stats[SaleAmt LY Sub])
SaleAmt % LY:= (DIVIDE((CALCULATE(Stats[SaleAmt TY])),(CALCULATE(Stats[SaleAmt LY])),1))-1

       

      However, i cant figure out SUMX equivalent for DIVIDE in case of Margin %. As sent earlier, below is where i am stuck. The issue is only with Total rows in this case. The Individual measure by Dates are working fine.

       

       

      Margin % TY := DIVIDE(Stats[MarginAmt TY],Stats[SaleAmt TY],1))
Margin % LY Sub:= CALCULATE(Stats[Margin % TY],FILTER(ALL('Date'),'Date'[Date] = MAX('Date'[LYDate])))

       

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        VamshiKrishna84 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

         

        Have tried using FY end date in datesytd

         CALCULATE([Margin %],DATESYTD('Date'[Date],"8/31")) // August to Jul Year

or
 CALCULATE([Margin %],DATESYTD('Date'[Date],"6/30")) // Jul to Jun year