Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Tavi_
Frequent Visitor

Cumulative Cartons Forecasted

Hi,

I use this formula for the cumulative total of forecasted cartons.

_Forecast Cartons Cumulative = CALCULATE(
[_Forecast Cartons],
    FILTER(
        ALL('[DIM] Date'[Date]),
        '[DIM] Date'[Date] <= MAX ('[DIM] Date'[Date]))
)
1111.png
 
But how do I adjust the formula so the formula starts counting from today? So my figures will look like this
2222.png
 
1 ACCEPTED SOLUTION

@Tavi_ OK, didn't understand that from the original post:

_Forecast Cartons Cumulative = 
VAR __Value = CALCULATE(
[_Forecast Cartons],
    FILTER(
        ALL('[DIM] Date'[Date]),
        '[DIM] Date'[Date] <= MAX ('[DIM] Date'[Date]) && '[DIM] Date'[Date] >= TODAY() )
)
VAR __Result = IF( MAX('[DIM] Date'[Date]) < TODAY(), BLANK(), __Value )
RETURN
__Result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Tavi_
Frequent Visitor

@Khushidesai0109 your formula shows me this figures
1111.png

 

@Greg_Deckler your formula shows me this figures
3333.png

 

Both not the figures I'm looking for. De formula must ignore al the cartons before today. 

@Tavi_ OK, didn't understand that from the original post:

_Forecast Cartons Cumulative = 
VAR __Value = CALCULATE(
[_Forecast Cartons],
    FILTER(
        ALL('[DIM] Date'[Date]),
        '[DIM] Date'[Date] <= MAX ('[DIM] Date'[Date]) && '[DIM] Date'[Date] >= TODAY() )
)
VAR __Result = IF( MAX('[DIM] Date'[Date]) < TODAY(), BLANK(), __Value )
RETURN
__Result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Khushidesai0109
Resolver I
Resolver I

Hii You can use this DAX Instead
Forecast Cartons Cumulative =
CALCULATE (
[_Forecast Cartons],
FILTER (
ALLSELECTED ( '[DIM] Date'[Date] ),
'[DIM] Date'[Date] <= MAX ( '[DIM] Date'[Date] )
)
)

If this solution helped you please give a thumbs up and accept this reply as a solution!!
Thank You!! Regards!!
 



Greg_Deckler
Super User
Super User

@Tavi_ 

_Forecast Cartons Cumulative = 
VAR __Value = CALCULATE(
[_Forecast Cartons],
    FILTER(
        ALL('[DIM] Date'[Date]),
        '[DIM] Date'[Date] <= MAX ('[DIM] Date'[Date]))
)
VAR __Result = IF( MAX('[DIM] Date'[Date]) < TODAY(), BLANK(), __Value )
RETURN
__Result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.