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.
Hi,
For calculating a forecast, I would like to create the average of % on the same working day of last 12 months.
I would be appreciated if you could help me. Thanks!!
Solved! Go to Solution.
Please try this one instead. I didn't notice before that you have the Month column in the visual (not dates).
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this expression
Avg Same WD =
VAR thisWD =
MIN ( 'Date'[WD 22] )
VAR thisdate =
MIN ( 'Date'[Date] )
RETURN
AVERAGEX (
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] <= EOMONTH ( thisdate, 0 )
&& 'Date'[Date] >= EOMONTH ( thisdate, -12 )
&& 'Date'[WD 22] = thisWD
),
[Percent Measure]
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat
Thank you for your quick response!!
I don´t know why but i can´t put "Calendar WD22" in the part below. How should I do??
My bad. Forgot about that column. Try this version.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat
Thank you very much but It seems to me that it don´t work well. There are something that I should change??
Please share the Collection % measure.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Signing off the the night. Will take a look again tomorrow, if someone else doesn't solve it first.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this one instead. I didn't notice before that you have the Month column in the visual (not dates).
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat
Thank you very much!! It woked well!! you are so smart.
Sorry, could you do me a favor?? (other matter)
For calculating forecast, I created the expression as below but I don't know how to insert today´s WD22 automatically (for example, today´s wd22 is 17. I tried to use calculate or lookupvalue but it didn´t work well.
Could you help me??
@yugofukuda - You can create this column:
IsWorkingDay = IF(WEEKDAY([Date],2) < 6,1,0)
And then this column will return the working day number of every day in each month:
WorkingDay# =
VAR __WorkingDay = COUNTROWS(FILTER('Calendar',YEAR([Date])=YEAR(EARLIER([Date])) && MONTH([Date])=MONTH(EARLIER([Date])) && [Date]<=EARLIER([Date]) && [IsWorkingDay]=1))
RETURN
IF([IsWorkingDay]=0,0,__WorkingDay)
PBIX is attached below sig.
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |