Minus 1 day in edate measure

[MaPBIAch2010]

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!

KR

Maha

[MaPBIAch2010 · Accepted solution]

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!

KR

Maha