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. I am new to power bi and would like to see if anyone is able to help me with figuring out a formula for a situation. Here is a example I just typed up in excel for this. If I wanted to create a formula that adds up how much is due this week (1/10 ordered column 8,000) plus what is remaining from previous weeks is there a formula that can do this? And if the Status column shows CLOSED that means nothing is due and the order is complete so is there a way for the formula to only look at OPEN columns? Thanks!
Solved! Go to Solution.
@Anonymous , Try the preferably using date table and use date from date table in visual
Calculate(sum(Table[ordered]) - Sum(Table[received]) , filter(all('Date'), 'Date'[Date] <= max('Date'[Date]) ),
filter(Table, Table[Status] <> "CLOSED") )
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hi, @Anonymous
If you want to see the data as of today, you can use Today().
Measure =
CALCULATE (
SUM ( 'Table'[Ordered] ) - SUM ( 'Table'[Received] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= TODAY ()
&& 'Table'[Status] <> "CLOSED"
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Try the preferably using date table and use date from date table in visual
Calculate(sum(Table[ordered]) - Sum(Table[received]) , filter(all('Date'), 'Date'[Date] <= max('Date'[Date]) ),
filter(Table, Table[Status] <> "CLOSED") )
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Thank you! i will try it out!
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 |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |