Forum Discussion

hchava's avatar
hchava
New Member
3 years ago

Year till date DAX query

Hi All,

I have a use case where i have to compare current year and previous year amount.I have 2 years of data .In the year of 2022 i have data for january from 01/01/2022 to 31/01/2022 and february from 01/02/2022 to 28/02/2022. In the year of 2023 i have data for january from 01/01/2023 to 31/01/2023 and february from 01/02/2023 to 06/02/2023(till date).Now i have to calculate previous year amount if i select year 2023 and month feb my previous year amount should calculate only till 06/02/2022 but not till 28/02/2022.

 

Below are the logics i used.

 

Cuurent year Amount=

TOTALYTD([Total Amount],'DATE'[Date])
Previous Year Amount=
calculate(sum(Sheet1[Amount]),sameperiodlastyear('Date'[DATE]))
 
But in previous year logic it is calculating amount for whole february month instead of calculating till 6th feb 2022.
 
Can someone help me in writing dax to calculate my previous year sales till feb 6th 2022.
 
 
Thanks in advance.
Need Help

1 Reply

  • LQuedas's avatar
    LQuedas
    Icon for Resolver II rankResolver II
    3 years ago

    Hi hchava ,

    I don't know how is your model, but assuming you have a Fact table with the values and a Calendar table

     

    i've  Created a source table with the following rows

    then I've created the calendar table with the CalendarAuto() function, so it means that the max date is the latest date with value. the I used the following simple DAX formula, this not the best approach as we have years with different # of days, but maybe can help you to create your own version.

     

    Previous YTD =
    CALCULATE (
        SUM ( FactValues[Value] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date]
                <= MAX ( FactValues[Date] ) - 365
        )
    )
     
    the result was:

     

    Hope this help you.

     

    Cheers, LQ