Forum Discussion

amandabus21's avatar
amandabus21
Icon for Helper V rankHelper V
3 years ago

Dax date help

Our fiscal year is July 1st - June 30th.    We currently have 2023 data for July - January.   I am trying to comparing last years 2022 data for July - January.   The formula below gives me wh...
andhiii079845's avatar
andhiii079845
Icon for Solution Sage rankSolution Sage
3 years ago

you can use a the max function. if you have a table with the current data and the new date.

you can say VAR startdate = max(table[datecolumn]) and you can also change your enddate if you want. 

VAR enddate =  DATEADD(table[datecolumn],-12,MONTH)
  • amandabus21's avatar
    amandabus21
    Icon for Helper V rankHelper V
    3 years ago
    I get this error:

     

     
     
    test =

    VAR StartDate = MAX(agency_daily_ridership[Merged FC].[Date])
    VAR EndDate =   DATEADD(agency_daily_ridership[Merged FC].[Date], -12, MONTH)
    RETURN
     {
         CALCULATE(
             CALCULATE( [Sum of KPI],
             SAMEPERIODLASTYEAR('agency_daily_ridership'[Merged FC].[Date]) ),
             'agency_daily_ridership'[Merged FC] >= StartDate &&
             'agency_daily_ridership'[Merged FC] <= EndDate
         )
     }