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.
Greetings!
I am having trouble getting Cumulative Max days.
To calculate the same I have used following measure:
This gives me Max days but not the cumulative result.
I have uploaded PBIX & Excel sample data file for reference
https://drive.google.com/drive/folders/1m3uGRUPN28hqKPab2oYSmzO91z9t0BT3?usp=sharing
Looking forward for a quick guidance please.
Thanks & best regards,
PG
Solved! Go to Solution.
@pankajj
I haven't exactly got what you're trying to do, but this measure will give you what you need
Cumulative Max Days =
CALCULATE(
COUNT(Dates[Date]),
FILTER(
ALLSELECTED(Dates),
Dates[Date] < MAX(Dates[Date] )
)
)
________________________
If my answer was helpful, consider Accepting it as the solution to help other members find it
Click the Thumbs-Up icon if you like this answer 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks a ton for your kind help. It worked perfectly with the sample file i shared.
In the meantime i created following measures to get the desired result in my existing data.
Open =
Was
(dates[Date])
RETURN
calculate(
CALCULATE(count(SN[ID])+0,
SN,
SN[Created]<=d
),
crossfilter(SN[Created],Dates[Date],None)
)
OLDEST_Open_Date =
Was
d - MAX (Dates[Date])
RETURN
CALCULATE(
FORMAT(CALCULATE(MIN(SN[Created]),
SN,
SN[Created]<=d),
"Short Date"
),
CROSSFILTER(SN[Created],Dates[Date],None)
)
Cumulative_Max_Days =
Was
d - Max (Dates[Date])
RETURN
CALCULATE(if([Open]>0,DATEDIFF(SN[OLDEST_Open_Date],d,Day),0))
@pankajj
For Cumulative Total, you need to use the SUM
Cumulative Max Days =
CALCULATE(
SUM(SN[DaysTillToday]),
FILTER(ALL ('Dates'), 'Dates'[Date] <= MAX('Dates'[Date]))
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks a ton for your quick reply.
Using Sum in the measure gives me following Result:
However I am looking for a result which should be something like:
Date | Cumulative Max Days |
September21 2020 | 13 |
September20 2020 | 12 |
September19 2020 | 11 |
September18 2020 | 10 |
September17 2020 | 9 |
September16 2020 | 8 |
September15 2020 | 7 |
September14 2020 | 6 |
September13 2020 | 5 |
September12 2020 | 4 |
September11 2020 | 3 |
September10 2020 | 2 |
September09 2020 | 1 |
September08 2020 | 0 |
@pankajj
Can you share sample data from the date and the fact table, is cumulative max days a measure?
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I have uploaded PBIX & Excel sample data file for reference
https://drive.google.com/drive/folders/1m3uGRUPN28hqKPab2oYSmzO91z9t0BT3?usp=sharing
Thanks once again for your help.
@pankajj
I haven't exactly got what you're trying to do, but this measure will give you what you need
Cumulative Max Days =
CALCULATE(
COUNT(Dates[Date]),
FILTER(
ALLSELECTED(Dates),
Dates[Date] < MAX(Dates[Date] )
)
)
________________________
If my answer was helpful, consider Accepting it as the solution to help other members find it
Click the Thumbs-Up icon if you like this answer 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks a ton for your kind help. It worked perfectly with the sample file i shared.
In the meantime i created following measures to get the desired result in my existing data.
Open =
Was
(dates[Date])
RETURN
calculate(
CALCULATE(count(SN[ID])+0,
SN,
SN[Created]<=d
),
crossfilter(SN[Created],Dates[Date],None)
)
OLDEST_Open_Date =
Was
d - MAX (Dates[Date])
RETURN
CALCULATE(
FORMAT(CALCULATE(MIN(SN[Created]),
SN,
SN[Created]<=d),
"Short Date"
),
CROSSFILTER(SN[Created],Dates[Date],None)
)
Cumulative_Max_Days =
Was
d - Max (Dates[Date])
RETURN
CALCULATE(if([Open]>0,DATEDIFF(SN[OLDEST_Open_Date],d,Day),0))
Sorry for the error, Html format has changed the table content.
I am looking for a result similar to :
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |