Forum Discussion

PhoenixBird66's avatar
PhoenixBird66
Helper III
6 years ago

DAX Help needed - Same Period Last Year

I have a graph whch looks at sales per month and compares it to the same period last year:

 

The DAX i used for that measure was:

 

Sales_SamePeriodLastYear = CALCULATE(SalesInvoiceLines[Sales], SAMEPERIODLASTYEAR('Date'[Date]))
 

The problem is that when it looks at the same period last year it counts the whole of the month, but obviously if I refresh the data on the 15th of the month, it is only half way through the month so it shows a bit decrease on the same period last year. 

 

Any idea how I can better show this data without it being misleading (showing a loss because its comparing half a month with a full month last period)? Or perhaps I can change the measure slightly so it looks at the actual date rather than the month?

Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    You can use datesytd, totalytd

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

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

     

     

    like

    Last YTD Sales = 
var _max = maxx(Sales, Sales[sales date])
var _maxly = date(year(_max)-1,month(_max),day(_max))

CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"),Sales[sales date]<=_maxly )

    or

     

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi  PhoenixBird66 

    For your case, just add a conditional in the formula as below:

    New Sales_SamePeriodLastYear = CALCULATE(SalesInvoiceLines[Sales], SAMEPERIODLASTYEAR('Date'[Date]),
FILTER(ALL('Date'[Date]),DATEADD('Date'[Date],1,YEAR)<=CALCULATE(MAX(SalesInvoiceLines[Date]),ALLSELECTED(SalesInvoiceLines))))

     

    Regards,

    Lin

    • PhoenixBird66's avatar
      PhoenixBird66
      Helper III
      6 years ago

      Thanks for all the suggestions guys but sadly I'm still getting the sales for the whole of March 2019, when what I want is the sales from 1st March 2019 up to 24th March 2019 - this date last year. 

       

      I'm stuck and just can't think how to do this.

      • PhoenixBird66's avatar
        PhoenixBird66
        Helper III
        6 years ago

        Here is a picture of the problem.

        Last year, March's sales included all dates in March (1st March 2019-31st March 2019), but this year we're not quite at the end of of the month so the column "SalesThis Year" only contains sales data from 1st March 2020 to 23rd March 2020. 

        I want the Same Period Last Year column to only show March sales between 1st March 2019 and 23rd March 2019 (instead of the full month)...