Weekly Monthly, Quarterly, Yearly Cumulative Sum till date selected

[vishal097]

Hello There,

I am trying to write a measure which will act as 

if selected Year = 2022 then 

• Weekly Cumulative = Values from start of the year till end of the year
• Month Cumulative = Values from start of the year till end of the year
• Quarter Cumulative = Values from start of the year till end of the year
• Yearly Cumulative = Values from start of the year till end of the year

if selected Year = 2022 and Quarter = Q2 then

• Weekly Cumulative = Values from April 2022 of the year till June 2022
• Month Cumulative = Values from April 2022 of the year till June 2022
• Quarter Cumulative = Values from April 2022 of the year till June 2022
• Yearly Cumulative = Values from Jan 2022 of the year till June 2022

if selected, Year = 2022 and Quarter = Q2 and Month = May then

• Weekly Cumulative = Values from May 2022 of the year till May 2022
• Month Cumulative = Values from May 2022 of the year till May 2022
• Quarter Cumulative = Values from April 2022 of the year till May 2022
• Yearly Cumulative = Values from Jan 2022 of the year till May 2022 

if selected Year = 2022 and Quarter = Q2 and Month = May and

Week = Week 18  ( which has date as [ (Monday) 2 May 2022 - 8 May 2022 (Sunday) my week start on Monday )

then

• Weekly Cumulative = Values from 2 May 2022 of the year till 8 May 2022
• Month Cumulative = Values from 1 May 2022 of the year till 8 May 2022
• Quarter Cumulative = Values from 1 April 2022 of the year till 8 May 2022
• Yearly Cumulative = Values from 1 Jan 2022 of the year till 8 May 2022

if selected Year = 2022 and Quarter = Q2 and Month = May and Week = Week 18 and Day = 4 May 2022

• Weekly Cumulative = Values from 2 May 2022 of the year till 4 May 2022
• Month Cumulative = Values from 1 May 2022 of the year till 4 May 2022
• Quarter Cumulative = Values from 1 April 2022 of the year till 4 May 2022
• Yearly Cumulative = Values from 1 Jan 2022 of the year till 4 May 2022

if selected Year = 2022 and Quarter = Q2 and Month = May and Week = Week 18 and Day = 3, and 4 May 2022 then 

• Weekly Cumulative = Values from 3 May 2022 of the year till 4 May 2022
• Month Cumulative = Values from 1 May 2022 of the year till 4 May 2022
• Quarter Cumulative = Values from 1 April 2022 of the year till 4 May 2022
• Yearly Cumulative = Values from 1 Jan 2022 of the year till 4 May 2022

I am trying from last two days could coudn't able to write a measure which can me give exact result 

I tried something like 

Planned Monthly W/o = 
Var MaxDate = EDATE(LASTDATE(Dim_Period_daywise[Date]), 0 )
Var MinDate = EOMONTH(MaxDate, -1)+1
Var Result =
CALCULATE(
    SUM( Fact_Production_VS_Quantity_T3[Planned]),
    DATESBETWEEN( Dim_Period_daywise[Date], MinDate , MaxDate )
)

Return
Result

Here is the attached PBI file. https://drive.google.com/file/d/1LZ4tg--5hxX4AVSzCGC5IwFG3g0qBBij/view?usp=sharing 

also would it be possible that this could work on odd day selection let's say 2, May and 4 may selected, ?

Can someone please help 🙂

[v-yetao1-msft]

Hi @vishal097 

From your formula, I extracted the max date and min date . When I choose year=2022 , the dates are shown as below . 

Please correct your measure like below .

Planned Monthly W/o = 
Var MaxDate = EDATE(LASTDATE(Dim_Period_daywise[Date]), 0 )
Var MinDate = MIN(Dim_Period_daywise[Date])
Var Result =
CALCULATE(
    SUM( Fact_Production_VS_Quantity_T3[Planned]),
    DATESBETWEEN( Dim_Period_daywise[Date], MinDate , MaxDate )
)
Return
Result

 Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vishal097]

Hello @v-yetao1-msft 

Thanks for responding but this does not work when I select second or third or fourth week number and it showa only that perticular week data and same thing goes if i select 1 perticular day