Filter by YTD and Month To Date

[PabloGiraldo]

I got help creating the following measures. Right now it filters everything to date. I would like to also be able to filter by YTD and Month To Date.

Link to the PBIX file below.  This would have been simple but it gets a bit complicated as the calculations need to happen based on the last non zero value of the Total MH Forecasted column.https://walshgroup-my.sharepoint.com/:u:/p/pgiraldoclavijo/EZrXBkrfYltLs0bUMvA1rFYB5iNhsgsqRz1IDIzeP... 

Thank you,

Pablo

[v-rzhou-msft]

Hi @PabloGiraldo 

Your measure will always show sum value in columns, if there has one value in week ending, and will show divide result in total.

For example:

Field measure = 
IF(
    HASONEVALUE('Date'[Week Ending]),
    IF(
        ISFILTERED('Table'[Week Ending]),
        SUM('Table'[Field]),
        DIVIDE(
            CALCULATE(
                SUM('Table'[Field]),
                FILTER(
                    ALL('Table'),
                    [Week Ending]<=SELECTEDVALUE('Date'[Week Ending])
                )
            ),
            CALCULATE(
                SUM('Table'[Total MH Forecasted]),
                FILTER(
                    ALL('Table'),
                    [Week Ending]=
                    CALCULATE(
                        MAX('Table'[Week Ending]),
                        FILTER(
                            ALL('Table'),
                            [Week Ending]<=SELECTEDVALUE('Date'[Week Ending])&&
                            [Total MH Forecasted]>0
                        )
                    )
                )
            )
        )
    )
)

It will always show the same result in your measures except in total.

If you want to calculate YTD and MTD, you just need to add a filter in you measure.

YTD will show the values from the begain of the Year to the Date-1. Filter is as below:

Measure=
...Calculate =(Sum(Table[Value]),
Filter(All(Table),[Week Ending]>=Date(Year(selectvalue(Date[Week End])),01,01)&&[Week Ending]< selectvalue(Date[Week End]))...

MTD will show the values from the begain of the select Month to the Date -1. Filter is as below:

Measure=
...Calculate =(Sum(Table[Value]),
Filter(All(Table),[Week Ending]>=Date(Year(selectvalue(Date[Week End])),Month(selectvalue(Date[Week End])),01)&&[Week Ending]< selectvalue(Date[Week End]))...

For more details about YTD and MTD: MTD, QTD, and YTD Values Explained

If this reply still couldn't help you solve your problem, please tell me your calculate logic.

OR you can show a Screenshot of the result you want.

This may make it easier for me to understand your requirement. 

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[PabloGiraldo]

Not sure if i am following the calculations you did. Did not get results i am looking for.

For example looking at the filter of 11/07/20 (First screenshot of this post), i want to get:

% Complete up to last month = (sum(10*40))/995156 = 0.00004019

% Complete YTD =   (sum(10*40))/995156 = 0.00004019. In this case both are the same since the project just started, BUT once 2021 calculations begin % Complete up to last month and % Complete YTD will be different.

thanks.

[v-rzhou-msft · Accepted solution]

Hi @PabloGiraldo 

I build % Complete up to last month SubA measure,YTD SubA meausre, MTDSubA measure.

% Complete up to last month SubA measure =
VAR _SELECTDATE =
    SELECTEDVALUE ( 'Date'[Week Ending] )
VAR _SUMSubA =
    CALCULATE (
        SUM ( 'Table'[SubA] ),
        FILTER ( ALL ( 'Table' ), [Week Ending] < _SELECTDATE )
    )
VAR _LastnotZero =
    CALCULATE (
        SUM ( 'Table'[Total MH Forecasted] ),
        FILTER (
            ALL ( 'Table' ),
            [Week Ending]
                = CALCULATE (
                    MAX ( 'Table'[Week Ending] ),
                    FILTER (
                        ALL ( 'Table' ),
                        [Week Ending] < SELECTEDVALUE ( 'Date'[Week Ending] )
                            && [Total MH Forecasted] > 0
                    )
                )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Date'[Week Ending] ),
        IF (
            HASONEVALUE ( 'Table'[Week Ending] ),
            SUM ( 'Table'[SubA] ),
            DIVIDE ( _SUMSubA, _LastnotZero )
        )
    )

YTD SubA measure =
VAR _SELECTDATE =
    SELECTEDVALUE ( 'Date'[Week Ending] )
VAR _SELECTYEAR =
    YEAR ( _SELECTDATE )
VAR _YTDSUMSubA =
    CALCULATE (
        SUM ( 'Table'[SubA] ),
        FILTER (
            ALL ( 'Table' ),
            [Week Ending] < _SELECTDATE
                && YEAR ( 'Table'[Week Ending] ) = _SELECTYEAR
        )
    )
VAR _LastnotZero =
    CALCULATE (
        SUM ( 'Table'[Total MH Forecasted] ),
        FILTER (
            ALL ( 'Table' ),
            [Week Ending]
                = CALCULATE (
                    MAX ( 'Table'[Week Ending] ),
                    FILTER (
                        ALL ( 'Table' ),
                        [Week Ending] < _SELECTDATE
                            && [Total MH Forecasted] > 0
                    )
                )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Date'[Week Ending] ),
        IF (
            HASONEVALUE ( 'Table'[Week Ending] ),
            SUM ( 'Table'[SubA] ),
            DIVIDE ( _YTDSUMSubA, _LastnotZero )
        )
    )

MTD SubA measure =
VAR _SELECTDATE =
    SELECTEDVALUE ( 'Date'[Week Ending] )
VAR _SELECTYEAR =
    YEAR ( _SELECTDATE )
VAR _SELECTMonth =
    MONTH ( _SELECTDATE )
VAR _MTDSUMSubA =
    CALCULATE (
        SUM ( 'Table'[SubA] ),
        FILTER (
            ALL ( 'Table' ),
            [Week Ending] < _SELECTDATE
                && YEAR ( 'Table'[Week Ending] ) = _SELECTYEAR
                && MONTH ( 'Table'[Week Ending] ) = _SELECTMonth
        )
    )
VAR _LastnotZero =
    CALCULATE (
        SUM ( 'Table'[Total MH Forecasted] ),
        FILTER (
            ALL ( 'Table' ),
            [Week Ending]
                = CALCULATE (
                    MAX ( 'Table'[Week Ending] ),
                    FILTER (
                        ALL ( 'Table' ),
                        [Week Ending] < _SELECTDATE
                            && [Total MH Forecasted] > 0
                    )
                )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Date'[Week Ending] ),
        IF (
            HASONEVALUE ( 'Table'[Week Ending] ),
            SUM ( 'Table'[SubA] ),
            DIVIDE ( _MTDSUMSubA, _LastnotZero )
        )
    )

Result is as below.

If I select 2020/11/07,

SubA measure = (40*11)/995156, 

% Complete up to last month SubA measure = (40*10)/995156,

YTD SubA meausre = (40*10)/995156 YTD start from 2020/01/01 to 2020/11/06,

MTDSubA measure = (40*0)/995156 MTD start from 2020/11/01 to 2020/11/06. (Don't contain select date)

If I select 2021/01/09,

SubA measure = (40*15)/997624, 

% Complete up to last month SubA measure = (40*14)/997624,

YTD SubA meausre = (40*1)/997624 YTD start from 2021/01/01 to 2021/01/08,

MTDSubA measure = (40*1)/997624 MTD start from 2021/01/01 to 2021/01/08,. (Don't contain select date)

Other YTD and MTD measures are similiar with the measure I built.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.