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.
I built a slicer from a Date table for work day count (non weekend/non holiday) for a month. It works great but it slices all months not just the current month. I want to show Average sales per Work day for all months in 2021 and keep Jan, Feb, March static since they are completed but for April (or any future current month) i want to see the average sales per work day at various points using the slicer. Show first 5 day average, first 10 day average, etc. I know i can use actual calendar dates to acheive this (slice first calendar date of data to the most recent), but i want to stick to workdays in the current month as it a much cleaner visual.
Solved! Go to Solution.
Hi, @Huskyjimbo
According to your description, I can roughly understand your requirement, I think you can achieve this using two table charts and a slicer, you can try my steps:
This is my test data after the filter in the PQ to display only workdays:
Date number = RANKX('Table',[Date],,ASC,Dense)
first 5 day average =
CALCULATE(
AVERAGE('Table'[Amount]),
FILTER(ALL('Table'),[Date number]<=MAX([Date number])&&[Date number]>=MAX([Date number])-5))
first 10 day average =
CALCULATE(
AVERAGE('Table'[Amount]),
FILTER(ALL('Table'),[Date number]<=MAX([Date number])&&[Date number]>=MAX([Date number])-10))
And you can get what you want, like this:
You can download my test pbix file below
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Huskyjimbo
According to your description, I can roughly understand your requirement, I think you can achieve this using two table charts and a slicer, you can try my steps:
This is my test data after the filter in the PQ to display only workdays:
Date number = RANKX('Table',[Date],,ASC,Dense)
first 5 day average =
CALCULATE(
AVERAGE('Table'[Amount]),
FILTER(ALL('Table'),[Date number]<=MAX([Date number])&&[Date number]>=MAX([Date number])-5))
first 10 day average =
CALCULATE(
AVERAGE('Table'[Amount]),
FILTER(ALL('Table'),[Date number]<=MAX([Date number])&&[Date number]>=MAX([Date number])-10))
And you can get what you want, like this:
You can download my test pbix file below
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Huskyjimbo
I am not sure how your data model and visualizations look like, but in my opinion, I suggest trying to create one more measure for non-complete-month calculation.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
115 | |
101 | |
72 | |
68 | |
45 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |