Forum Discussion

Connerf's avatar
Connerf
Frequent Visitor
7 years ago
Solved

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:   Retur...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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 )
    )

     

AlB's avatar
AlB
Community Champion
7 years ago

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 )
    )
AlB's avatar
AlB
Community Champion
7 years ago

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 )
    )