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 all,
if i have a table which comprises of a date field and a cumulative column field, is there a measure i can use to calculate the actual daily figures on a day by day basis.
need this to work on both a table and a graph.
Got this working for a table but line graph just showed the cumulative figure when i used the daily actual confirm measure. so the graph showed values in the 20000 range instead of the 0-100 range
Country/Region | Date | Yesterdays Confirmed | Confirmed | Daily actuals confirm |
Angola | 28/01/2021 00:00 | 19580 | 19672 | 92 |
Angola | 27/01/2021 00:00 | 19553 | 19580 | 27 |
Angola | 26/01/2021 00:00 | 19476 | 19553 | 77 |
Solved! Go to Solution.
Hi, @lewil
According to your description, you said that the measure [daily actuals confirm] can display the correct value when it’s placed in a table chart, but incorrect in the line chart. Right? In my opinion, if you have created the measure [Confirmed] and [Yesterdays Confirmed], you can just calculate the [daily actuals confirm] like this:
Daily actuals confirm =
[Confirmed1]-[Yesterdays Confirmed1]
Then I created a column chart and place like this, the measure can display the correct value, like this:
And you can get what you want.
If you still have a problem, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
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, @lewil
According to your description, you said that the measure [daily actuals confirm] can display the correct value when it’s placed in a table chart, but incorrect in the line chart. Right? In my opinion, if you have created the measure [Confirmed] and [Yesterdays Confirmed], you can just calculate the [daily actuals confirm] like this:
Daily actuals confirm =
[Confirmed1]-[Yesterdays Confirmed1]
Then I created a column chart and place like this, the measure can display the correct value, like this:
And you can get what you want.
If you still have a problem, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
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.
@lewil add a new column using the following DAX expression and it will get your daily value from the cumulative total.
Sales =
VAR __currentDate = Sales[Date]
VAR __previousDate =
CALCULATE (
LASTDATE ( Sales[Date] ),
Sales[Date] < __currentDate
)
VAR __previousSales =
CALCULATE (
MAX ( Sales[Cumulative Sales] ),
Sales[Date] = __previousDate
)
RETURN
Sales[Cumulative Sales] - __previousSales
Change table and column name as per your model.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@lewil , You can try a measure like
sum(Table[Confirmed]) - sumx(filter(Table, [Country/Region] = max([Country/Region]) && [Date] =max([Date]) -1),[Confirmed])
or try time intelligence
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |