topic Re: Date Variable wrong format in DAX Commands and Tips

Date Variable wrong format

Stabben23 — Wed, 08 Sep 2021 12:52:08 GMT

Hi,

I want to create a variable in my DAX formula.

Var = maxdate= max(DateTable[Date])-7, this should gives me the maxdate from selected slicer in YYYY-MM-DD format but it gives me a numeric value for the date. So when I try to filter DateTable <= maxdate it doesent filter.

If I define the Variable like Date(2021-09-01) everything works fine.

Re: Date Variable wrong format

Greg_Deckler — Wed, 08 Sep 2021 13:53:52 GMT

Stabben23 That's odd. I can't replicate that behavior with a date table with a Date column.

Re: Date Variable wrong format

Samarth_18 — Wed, 08 Sep 2021 13:58:17 GMT

HI Stabben23 ,

You can try below code:-

Measure = VAR selected_date = SELECTEDVALUE ( table[date] ) VAR maxx_date = DATEVALUE ( FORMAT ( selected_date - 7, "YYYY-MM-DD" ) ) RETURN maxx_date

Thanks,

Samarth

Re: Date Variable wrong format

Stabben23 — Wed, 08 Sep 2021 14:14:29 GMT

I think I have to pologize, I work in Power Pivot, normally Power BI. And I now realize that these measure doesent act the same. All answers is probably correct. But in Power Pivot it fails when I put -7 in the formula.

If I skip -7 it shows me a results, but I can not find a way to offset my enddate.