Weekly Monthly, Quarterly, Yearly Cumulative Sum till date selected

vishal097 — Sun, 02 Oct 2022 17:39:48 GMT

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 🙂

Re: Weekly Monthly, Quarterly, Yearly Cumulative Sum till date selected

Anonymous — Mon, 03 Oct 2022 08:21:06 GMT

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.

Re: Weekly Monthly, Quarterly, Yearly Cumulative Sum till date selected

vishal097 — Mon, 03 Oct 2022 08:52:14 GMT

Hello Anonymous

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

topic Re: Weekly Monthly, Quarterly, Yearly Cumulative Sum till date selected in DAX Commands and Tips

Weekly Monthly, Quarterly, Yearly Cumulative Sum till date selected

Re: Weekly Monthly, Quarterly, Yearly Cumulative Sum till date selected

Re: Weekly Monthly, Quarterly, Yearly Cumulative Sum till date selected