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!
I have a simple line bar chart that displays the latest information (Sum Value) vs the Budget.
The line represents the budget accumulated per day for the selected month.
The bar represents the actual sales.
Now I would like to display a bar chart for the current month with a line for the full month but bars just for the past and today.
This measure displays the running total sum until today also for the next days.
How can I cut this by today?
Runnint Total =
CALCULATE(
SUMX(
'Calendar',
[Sales]
),
FILTER(
ALL( 'Calendar' ),
'Calendar'[Year]
= VALUES( 'Calendar'[Year] )
&& 'Calendar'[Month Num]
= MAX( 'Calendar'[Month Num] )
&& 'Calendar'[Date]
<= MAX( 'Calendar'[Date] )
)
)
Solved! Go to Solution.
@joshua1990 Better Running Total - Microsoft Power BI Community
Hi @joshua1990 ,
I created some data:
Here are the steps you can follow:
1. Create measure.
past and today =
IF(
MAX('Calendar'[Date])>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
&&
MAX('Calendar'[Date])<=TODAY(),
SUMX(
FILTER(ALL('Calendar'),
'Calendar'[Date]<=MAX('Calendar'[Date])&&'Calendar'[Year]=MAX('Calendar'[Year])&&'Calendar'[Month Num]=MAX('Calendar'[Month Num])),[Sales]),BLANK())
full month =
IF(
MAX('Calendar'[Date])>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
&&
MAX('Calendar'[Date])<=TODAY(),
SUMX(
FILTER(ALL('Calendar'),
'Calendar'[Year]=MAX('Calendar'[Year])&&'Calendar'[Month Num]=MAX('Calendar'[Month Num])),[Sales]),BLANK())
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@joshua1990 Better Running Total - Microsoft Power BI Community
@Greg_Deckler : Thanks a lot for sharing this alternative. But please note, my question is different > How can I cut a running total chart for the whole month (distributed per day) but with just values for the past?
Using your approach I still get a values for tomorrow etc.
@joshua1990 Handling tomorrow values is pretty easy, you just need an IF statement.
// Your code that results in a running total in a VAR named __RT
VAR __Result = IF(MAX('Date'[Date]) > TODAY(),BLANK(),__RT)
RETURN
__Result
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |