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'm a pretty new poster here so apologies if I've not posted or given examples in the right way.
I'm looking for some help with my measure to have my charts show correctly. I will give examples as best I can but it is commerically sensitive data so can't share the actual values.
I have a dataset showing hires, daily hire rates and the start and end date of the hire in question. I am trying to add up the total value of all of the daily hire rates that are active in a period. See a simplified example below.
Hire Daily Rate Hire Start Hire End
Project 1 £100 6th March 19th March
Project 2 £150 13th March 26th March
These are 2nr, 2 week hires with the middle week overlapping. So I would want the following results using a daily and then a weekly 'bin' on my report
Date Daily Total
6th March £100
7th March £100
8th March £100
9th March £100
10th March £100
11th March £100
12th March £100
13th March £250
14th March £250
15th March £250
16th March £250
17th March £250
18th March £250
19th March £250
20th March £150
21st March £150
22nd March £150
23rd March £150
24th March £150
25th March £150
26th March £150
w/c Weekly Total
6th March £700
13th March £1,750
20th March £1,050
If I use a claulated column in my Date table then these calculations work but then they are not compatible with any slicers. If I use a measure in the same table then they just don't add up correctly and the value of my 'bin' whether I set to days, weeks, months or years gives me pretty much the same total which I 'think' is an average rather than a total sum.
My measure is set as below. Can anyone see my error please?
Solved! Go to Solution.
Hi,
I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
I hope the below can provide some ideas on how to create a solution for your datamodel.
Daily total: =
SUMX (
DISTINCT ( 'Calendar'[Date] ),
CALCULATE (
SUMX (
FILTER (
Project,
Project[Hire start] <= MAX ( 'Calendar'[Date] )
&& Project[Hire end] >= MIN ( 'Calendar'[Date] )
),
Project[Daily rate]
)
)
)
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.
Hi,
I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
I hope the below can provide some ideas on how to create a solution for your datamodel.
Daily total: =
SUMX (
DISTINCT ( 'Calendar'[Date] ),
CALCULATE (
SUMX (
FILTER (
Project,
Project[Hire start] <= MAX ( 'Calendar'[Date] )
&& Project[Hire end] >= MIN ( 'Calendar'[Date] )
),
Project[Daily rate]
)
)
)
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.
@Jihwan_Kim Thats Awesome, thank you. It works perfectly once tweaked for my column names.
You've saved my sanity.
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 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |