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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Connerf
Frequent Visitor

Month to Go Calculation

Hey Power BI Team,

 

I'm looking for a bit of help regarding a month to go (MTG (no not magic the gathering)) calculation. The calculation needs to accomplish the following three things:

 

  1. Return a BLANK() for any values < TODAY()
  2. Return [Total Schedule] for only the days remaining in the month
  3. Return [Total Schedule] for months beyond the current month

(I've figured out 2/3 items above)

 

Sample Data:

DATE(DimDate)  Total Schedule(Fact Schedule)

2019-05-01         100

...

2019-05-30         100

2019-05-31         100

 

For May 2019, the [MTG Schedule] needs to = 200. However, for dates in June and beyond, it should return the [Total Schedule].

 

Current Code:

MTG Production = 

var maxDate = EOMONTH(TODAY(), 0)

RETURN

CALCULATE(
    [Total Schedule],
    FILTER(
        'Date', 'Date'[Date] >= TODAY() && 'Date'[Date] <= maxDate
    )
)

For whatever reason, I can't figure out how to wrap the above calculation in an IF statement that would just return [Total Schedule] when the MONTH([Date[Date]) > MONTH(TODAY()).

 

Thanks for your help.

1 ACCEPTED SOLUTION

@Connerf 

Ok, try this slight change then. Remember to always show an example based on your data to explain what you need. It is very helpful for those trying to provide an answer

TG Production =
VAR maxDate =
    IF (
        MONTH ( TODAY () ) = MONTH ( MAX('Date'[Date] )),
        EOMONTH ( TODAY (), 0 ),
        MAX ( 'Date'[Date] )
    )
RETURN
    CALCULATE (
        [Total Schedule],
        FILTER ( 'Date', 'Date'[Date] >= TODAY () && 'Date'[Date] <= maxDate )
    )

 

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

To your visual, drag Date from the Date Table.  Try this measure

=IF(MAX('Date'[Date])<=TODAY(),CALCULATE([Total Schedule],DATESBETWEEN('Date'[Date],TODAY(),EOMONTH(TODAY(),0))),[Total Schedule])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
AlB
Super User
Super User

Hi @Connerf 

I'm not sure I understand completely what you're after. Is it a measure that you want, to be used with dates in the rows of a matrix visual? Do you want [Total schedule] evaluated betweeen today and the end of the current month or up to the date on the current row for future months? If so:

 

TG Production =
VAR maxDate =
    IF (
        MONTH ( TODAY () ) = MONTH ( SELECTEDVALUE('Date'[Date] )),
        EOMONTH ( TODAY (), 0 ),
        SELECTEDVALUE ( 'Date'[Date] )
    )
RETURN
    CALCULATE (
        [Total Schedule],
        FILTER ( 'Date', 'Date'[Date] >= TODAY () && 'Date'[Date] <= maxDate )
    )

@Connerf 

Ok, try this slight change then. Remember to always show an example based on your data to explain what you need. It is very helpful for those trying to provide an answer

TG Production =
VAR maxDate =
    IF (
        MONTH ( TODAY () ) = MONTH ( MAX('Date'[Date] )),
        EOMONTH ( TODAY (), 0 ),
        MAX ( 'Date'[Date] )
    )
RETURN
    CALCULATE (
        [Total Schedule],
        FILTER ( 'Date', 'Date'[Date] >= TODAY () && 'Date'[Date] <= maxDate )
    )

 

Connerf
Frequent Visitor

@AlB,

 

Thanks for your work on this. 

 

I do want the measure to be used in a matrix and column chart. For the current month, it should just show [Total Schedule] from TODAY() until EOM. For months in the future, it should just show [Total Schedule].

 

The code you provided works when I've drilled down to the lowest level ('Date'[Date]), however, how do I get the code to work when I roll up to a week or month?

 

Thanks,

C

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.