Forum Discussion

Neyney's avatar
Neyney
Frequent Visitor
1 year ago
Solved

Current Year versus Last Year Same time thi

I need to do a Current Year versus Last Year (based on the max date available this current year) ---- My LY YTD SALES formula seems off . My Current Year calculation (CY) based on the max date available on my dataset is working well, which is 1/11/2025, but when I put Last Year's formula in my visual it's showing me all year to December, but I need to see that data from last Year up to the same period this year. My formula seems off, but I can't figure out what it is. Here is what I am using, I tried different formula's but can't get a proper comparison of up to same data side by side:

LY YTD Sales =
CALCULATE(
    [All Sales Sum],
    'Paid Date Table'[Date] >= DATE(YEAR(TODAY()) - 1, 1, 1),
    'Paid Date Table'[Date] <= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY())),
    'Paid Date Table'[IsPast] = TRUE()
)

 

I tried this one too:

 

LY YTD Sales =
CALCULATE(
    [All Sales Sum],
    DATESYTD('Paid Date Table'[Date]
))

 

 

I've tried this one too, but I know it's off since it is using same period last year, but need it to calculate up to same period of current year:

 

PY  =

CALCULATE(

    [All Sales Sum],

    SAMEPERIODLASTYEAR(

        DATESYTD('Paid Date Table'[Date])

    )

)


I've tried this one too, but doesn't work either:

 

LY YTD Sales =

CALCULATE(

    [All Sales Sum],

    'Paid Date Table'[Date] >= DATE(YEAR(MAX('Paid Date Table'[Date])) - 1, 1, 1),

    'Paid Date Table'[Date] <= DATE(YEAR(MAX('Paid Date Table'[Date])) - 1, MONTH(MAX('Paid Date Table'[Date])), DAY(MAX('Paid Date Table'[Date])))

)

 

 

I've tried different variations, but nothing seems to work on my end.

Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    1 year ago

    Hi, Neyney 

    Ty using Dates between, 

    refer, https://learn.microsoft.com/en-us/dax/datesbetween-function-dax

    use this one below, 

    LY YTD Sales =
    CALCULATE(
        [TotalSales],
        DATESBETWEEN(
            'Sample_YTD_Sales_Data'[Paid Date],
            DATE(YEAR(MAX('Sample_YTD_Sales_Data'[Paid Date])) - 1, 1, 1),
            DATE(YEAR(MAX('Sample_YTD_Sales_Data'[Paid Date])) - 1, MONTH(MAX('Sample_YTD_Sales_Data'[Paid Date])), DAY(MAX('Sample_YTD_Sales_Data'[Paid Date])))
        ),
        'Sample_YTD_Sales_Data'[IsPast] = TRUE()
    )
     

     

    attached the pbix file for you.

2 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    1 year ago

    Hi, Neyney 

    Ty using Dates between, 

    refer, https://learn.microsoft.com/en-us/dax/datesbetween-function-dax

    use this one below, 

    LY YTD Sales =
    CALCULATE(
        [TotalSales],
        DATESBETWEEN(
            'Sample_YTD_Sales_Data'[Paid Date],
            DATE(YEAR(MAX('Sample_YTD_Sales_Data'[Paid Date])) - 1, 1, 1),
            DATE(YEAR(MAX('Sample_YTD_Sales_Data'[Paid Date])) - 1, MONTH(MAX('Sample_YTD_Sales_Data'[Paid Date])), DAY(MAX('Sample_YTD_Sales_Data'[Paid Date])))
        ),
        'Sample_YTD_Sales_Data'[IsPast] = TRUE()
    )
     

     

    attached the pbix file for you.

    • Neyney's avatar
      Neyney
      Frequent Visitor
      1 year ago

      Here is my updated formula based on your suggestion:

      LY YTD Sales =
      CALCULATE(
          [All Sales Sum],
          DATESBETWEEN(
              'Paid Date Table'[Date],
              DATE(YEAR(MAX('Paid Date Table'[Date])) - 1, 1, 1),
              DATE(YEAR(MAX('Paid Date Table'[Date])) - 1, MONTH(MAX('Paid Date Table'[Date])), DAY(MAX('Paid Date Table'[Date])))
          ),
          'Paid Date Table'[IsPast] = TRUE()
      )
       
       
      The field is now blank. What could be the problem? YOur help is much appreciated!