Forum Discussion
Formula help
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!
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.
3 Replies
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.
