topic Re: Dax date help in DAX Commands and Tips

Dax date help

amandabus21 — Wed, 15 Mar 2023 19:53:28 GMT

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

)

}

Re: Dax date help

andhiii079845 — Wed, 15 Mar 2023 20:20:24 GMT

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)

Re: Dax date help

amandabus21 — Wed, 15 Mar 2023 20:44:51 GMT

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

)

}

Re: Dax date help

andhiii079845 — Wed, 15 Mar 2023 21:05:20 GMT

My fault, enddate is wrong. The solution:

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

Re: Dax date help

amandabus21 — Wed, 15 Mar 2023 21:17:57 GMT

Just getting a blank value.

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

Re: Dax date help

amandabus21 — Wed, 15 Mar 2023 23:21:29 GMT

basically

VarStart Date = Start of last fiscal year

end date = lastest date available in data table

any help is apprecaietd!

Re: Dax date help

andhiii079845 — Thu, 16 Mar 2023 06:01:45 GMT

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

Re: Dax date help

amandabus21 — Thu, 16 Mar 2023 14:44:58 GMT

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.