Forum Discussion

amandabus21's avatar
amandabus21
Helper 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 what I need. However, is there a way I can automate to alwayas compare available dates from this year?
 
for example, when we get february, i I dont want to have to go in every month and change the formula to end date "2022, 02,28"
 
 
 
Last Year comparsion to this period =

VAR StartDate = DATE ( 2021,07,01 )
VAR EndDate =   DATE ( 2022, 01, 31 )

RETURN
 {
     CALCULATE(
         CALCULATE( [Sum of KPI],
         SAMEPERIODLASTYEAR('Dim DimDate'[Full_Date_Alternate_Key].[Date]) ),
         'Dim DimDate'[Full_Date_Alternate_Key] >= StartDate &&
         'Dim DimDate'[Full_Date_Alternate_Key] <= EndDate
     )
 }

7 Replies

  • andhiii079845's avatar
    andhiii079845
    Solution 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
      Helper 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
           )
       }
  • andhiii079845's avatar
    andhiii079845
    Solution Sage
    3 years ago

    My fault, enddate is wrong. The solution:

    VAR EndDate = DATE(YEAR(StartDate), MONTH(StartDate)-12, DAY(StartDate))

     

  • amandabus21's avatar
    amandabus21
    Helper V
    3 years ago

    Just getting a blank value. 

    Is the start date, giving me a start date of the last fiscal year? (July 1st)

  • amandabus21's avatar
    amandabus21
    Helper V
    3 years ago

    basically

    VarStart Date = Start of last fiscal year

    end date  = lastest date available in data table 

    any help is apprecaietd!

     

    • andhiii079845's avatar
      andhiii079845
      Solution Sage
      3 years ago

      Can you please post a data example for borh tables: How you define start last fiscal year? 

      • amandabus21's avatar
        amandabus21
        Helper V
        3 years ago

        We are currently in Fiscal Year 2023. So Calenadr Dates:  July 2022 - June 2023. 

         

        Last 2022 fiscal year would be: Calendar dates : July 2021 - June 2022

         

        We have data for 2023 fiscal year and want to compare it to how we were doing this time last year.