Forum Discussion
vinothkumar1990
Helper II
3 years agoCumulative data in Dax
Hi, I have 3 fileds which are Task, Planned Start Date and Planned End Date. I want calculate the cumulative count of Task based on the weekend(All Previous week+ Current week). Raw Data: Ta...
- 3 years ago
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Planned start date count: = IF ( MIN ( 'Calendar'[Date] ) + 7 >= MIN ( Data[Planned Start Date] ) && MAX ( 'Calendar'[Date] ) - 7 <= MAX ( Data[Planned End Date] ), CALCULATE ( COUNTROWS ( FILTER ( Data, Data[Planned Start Date] <= MAX ( 'Calendar'[Date] ) && Data[Planned Start Date] >= MIN ( 'Calendar'[Date] ) ) ), WINDOW ( 1, ABS, 0, REL, ALL ( 'Calendar'[WK enddate] ), ORDERBY ( 'Calendar'[WK enddate], ASC ) ) ) )Planned end date count: = IF ( MIN ( 'Calendar'[Date] ) + 7 >= MIN ( Data[Planned Start Date] ) && MAX ( 'Calendar'[Date] ) - 7 <= MAX ( Data[Planned End Date] ), CALCULATE ( COUNTROWS ( FILTER ( Data, Data[Planned End Date] <= MAX ( 'Calendar'[Date] ) && Data[Planned End Date] >= MIN ( 'Calendar'[Date] ) ) ), WINDOW ( 1, ABS, 0, REL, ALL ( 'Calendar'[WK enddate] ), ORDERBY ( 'Calendar'[WK enddate], ASC ) ) ) )
Jihwan_Kim
Super User
3 years agoHi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Planned start date count: =
IF (
MIN ( 'Calendar'[Date] ) + 7
>= MIN ( Data[Planned Start Date] )
&& MAX ( 'Calendar'[Date] ) - 7
<= MAX ( Data[Planned End Date] ),
CALCULATE (
COUNTROWS (
FILTER (
Data,
Data[Planned Start Date] <= MAX ( 'Calendar'[Date] )
&& Data[Planned Start Date] >= MIN ( 'Calendar'[Date] )
)
),
WINDOW (
1,
ABS,
0,
REL,
ALL ( 'Calendar'[WK enddate] ),
ORDERBY ( 'Calendar'[WK enddate], ASC )
)
)
)
Planned end date count: =
IF (
MIN ( 'Calendar'[Date] ) + 7
>= MIN ( Data[Planned Start Date] )
&& MAX ( 'Calendar'[Date] ) - 7
<= MAX ( Data[Planned End Date] ),
CALCULATE (
COUNTROWS (
FILTER (
Data,
Data[Planned End Date] <= MAX ( 'Calendar'[Date] )
&& Data[Planned End Date] >= MIN ( 'Calendar'[Date] )
)
),
WINDOW (
1,
ABS,
0,
REL,
ALL ( 'Calendar'[WK enddate] ),
ORDERBY ( 'Calendar'[WK enddate], ASC )
)
)
)