Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello There,
I am trying to write a measure which will act as
if selected Year = 2022 then
if selected Year = 2022 and Quarter = Q2 then
if selected, Year = 2022 and Quarter = Q2 and Month = May then
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
if selected Year = 2022 and Quarter = Q2 and Month = May and Week = Week 18 and Day = 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
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 🙂
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |