Forum Discussion

ganiu_s's avatar
ganiu_s
New Member
2 years ago

Previous year calculation

I have this query that is meant to calculate the data from last year every time i select the current year it should get previous data without having to hard code the year into the dax 

/*
VAR TodayDate = TODAY() - 364
VAR LastSaturday = TodayDate - WEEKDAY(TodayDate, 2) -- Ensures the last Saturday
VAR CurrentFinancialYear = SELECTEDVALUE(trading_data[DepartureFinancialYear])
 
VAR CurrentYearStart = LEFT(CurrentFinancialYear, 4)
VAR CurrentYearEnd = RIGHT(CurrentFinancialYear, 2)
VAR PreviousYearStart = VALUE(CurrentYearStart) - 1
VAR PreviousYearEnd = VALUE(CurrentYearEnd) - 1
VAR PreviousFinancialYear = FORMAT(PreviousYearStart, "0000") & "/" & FORMAT(PreviousYearEnd, "00")

RETURN
    CALCULATE(
        SUM(data[productCount]),
        FILTER(
           data,
            data[HistoryDateFrom] <= LastSaturday &&
            data[HistoryDateTo] >= LastSaturday &&
            data[Status] IN { "ok", "verify" } &&
           data[DepartureFinancialYear] = PreviousFinancialYear
        )
    )

*/
The query above returns blank  but this other query below returns values but when filtered 2024/25 it returns information for2023/24 that is incomplete 

Essentially its not going back to 2023/24 to obtain the records 
VAR TodayDate = TODAY() - 364
VAR LastSaturday = TodayDate - WEEKDAY(TodayDate)
                   -- DATE(2023, 5, 27)
--VAR WeekendDate = LastSaturday - 7
RETURN
    CALCULATE (
        Sum(trading_data[ProductCount]),
        FILTER (
            'data',
            data'[HistoryDateFrom] <= LastSaturday  &&
            'trading_data'[HistoryDateTo] >= LastSaturday
           -- && trading_data[ConfirmedDate]>= WeekStartDate && trading_data[ConfirmedDate]<= LastSaturday
           && 'trading_data'[Status] IN { "ok", "verified" }
           && trading_data[DepartureFinancialYear] = "2023/24" (needs to be dynamic )
        )

)


I have attched output as a further example 

selecting 2024/25 i want to be able to see data for 2023/24  and available data for 2024/25 in 2023/24

 




 


 



Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi ganiu_s  - Create a Date Table (if not already done) using new table Dax.

    Mark DateTable as a Date table by going to Table tools >> Mark as Date Table, and select the Date column.

    create a new measure

    VolumePreviousYear =
    VAR SelectedYear = SELECTEDVALUE('DateTable'[Year]) - 365
    RETURN
    CALCULATE(
    COUNT('YourTableName'[ID]),
    FILTER(
    ALL('DateTable'),
    'DateTable'[Year] = SelectedYear
    )
    )

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!