topic Re: Minus 1 day in edate measure in DAX Commands and Tips

Minus 1 day in edate measure

Anonymous — Thu, 21 Sep 2023 09:45:32 GMT

Hi all,

I found for my case the half way to complete solution, but now now I'm stuck.

I have below information, but on the end I need a solution, that the edate is minus 1 day.
This is my information Visual, it contain follow measures:

Runtime mth = (sum(Activities[Project_Runtime])*12)

SOP sdate = FIRSTDATE(Activities[SOP_Year])

SOP edate = IF((EDATE((MAX(Activities[SOP_Year])),[Runtime mth])) = Date(1900,12,31),"",(EDATE((MAX(Activities[SOP_Year])),[Runtime mth])))

The calculation by above measures look in the table correct.

But when I use below measure

Potential Sum per mth = SUMX(VALUES('Calendar'[YYYY-MM]), CALCULATE(SUMX(FILTER(Companies, Companies[SOP sdate] <= max('Calendar'[Date]) && Companies[SOP edate] >= MIN ('Calendar'[Date])), DIVIDE(Activities[Potential total SOP LC], DATEDIFF(Companies[SOP sdate], Companies[SOP edate],MONTH)))))

The Matrix visuel show the 5.000,00 to much in 2029 and the total is 365.000,00

I tried to subtract a day from the edate (-1 day), but can't get it to work.

Can someone help with me?

Thank u in advance!

Maha

Re: Minus 1 day in edate measure

Anonymous — Fri, 22 Sep 2023 05:52:50 GMT