Show 2 Month Variance

[Anonymous]

I think I'm overcomplicating this....

I basically have 3 visuals I need to interact correctly:

1. Matrix with dimDate dates (using PBI hierarchy)

2. Waterfall chart showing prior 3 months

3. Bar chart showing variance between 2 months

I want everything to show in this context:

1. If no date filter, show max month

2. If date filter, use max month (i.e. click February in matrix and waterfall will show Dec, Jan, Feb and barchart show Jan to Feb variance)

One of the big caveats is that I want to show the month regardless of how the data looks within filtered sets so if a location doesn't actually have data in the selected month I still need that month to show instead of just MAX(date).

The possible interactions are the following:

1. Clicking a date period in the matrix (dimDate field)

2. Clicking a variance in the waterfall chart which will crossfilter to those 2 periods but the filter applied is not a dimDate field. It's this calculated column in the fact table:

Calc_YearMonth = RELATED(dimDate[YearMonth])

The formulas for the bar chart are below and seem to be the ones giving me the most trouble. Since they're so long now I'm assuming I've done something wrong.

Measure_PersonsServed_Rolling_Current =
VAR WFSelected =
    CALCULATE (
        MAX ( fctPersonsServed[FiscalDate] ),
        REMOVEFILTERS ( dimLocation )
    )
RETURN
    IF (
        // Show amount for the selected month
        ISFILTERED ( dimDate[DateValue].[Month] )
            || ISFILTERED ( dimDate[DateValue].[Quarter] )
            || ISFILTERED ( dimDate[YearMonth] ),
        CALCULATE (
            [Measure_PersonsServed_SUM],
            FILTER (
                fctPersonsServed,
                fctPersonsServed[StatType] = "PersonsServed"
                    && fctPersonsServed[FiscalDate] = MAX ( fctPersonsServed[FiscalDate] )
            )
        ),
        // Show amount based on waterfall chart having a filter applied
        IF (
            ISFILTERED ( fctPersonsServed[Calc_YearMonth] ),
            CALCULATE (
                [Measure_PersonsServed_SUM],
                FILTER (
                    fctPersonsServed,
                    fctPersonsServed[StatType] = "PersonsServed"
                        && fctPersonsServed[FiscalDate] = WFSelected
                )
            ),
            // Show amount for the max fiscal date - need it to be max overall and not the max within each location, group, etc
            CALCULATE (
                [Measure_PersonsServed_SUM],
                FILTER (
                    fctPersonsServed,
                    fctPersonsServed[FiscalDate]
                        = CALCULATE (
                            MAX ( fctPersonsServed[FiscalDate] ),
                            FILTER (
                                ALL ( fctPersonsServed ),
                                fctPersonsServed[StatType] = "PersonsServed"
                            )
                        )
                )
            )
        )
    )

Measure_PersonsServed_Rolling_Prior =
VAR WFPrior =
    CALCULATE (
        MAX ( fctPersonsServed[FiscalDate] ),
        REMOVEFILTERS ( dimLocation )
    )
RETURN
    IF (
        // Show amount for previous month if a month is selected. Selecting a month will filter dimDate to that specific date period
        ISFILTERED ( dimDate[DateValue].[Month] )
            || ISFILTERED ( dimDate[DateValue].[Quarter] )
            || ISFILTERED ( dimDate[YearMonth] ),
        CALCULATE ( [Measure_PersonsServed_SUM], PREVIOUSMONTH ( dimDate[DateValue] ) ),
        // If the waterfall chart is filtered adjust accordingly
        IF (
            ISFILTERED ( fctPersonsServed[Calc_YearMonth] ),
            CALCULATE (
                fctPersonsServed[Measure_PersonsServed_SUM],
                FILTER (
                    fctPersonsServed,
                    fctPersonsServed[FiscalDate] = EOMONTH ( WFPrior, -1 )
                        && fctPersonsServed[StatType] = "PersonsServed"
                )
            ),
            // If no month is selected, display the previous month from the max fiscal date.
            CALCULATE (
                [Measure_PersonsServed_SUM],
                FILTER (
                    fctPersonsServed,
                    fctPersonsServed[FiscalDate]
                        = EOMONTH (
                            CALCULATE (
                                MAX ( fctPersonsServed[FiscalDate] ),
                                FILTER (
                                    ALL ( fctPersonsServed ),
                                    fctPersonsServed[StatType] = "PersonsServed"
                                )
                            ),
                            -1
                        )
                )
            )
        )
    ) 

[v-diye-msft]

Hi @Anonymous 

You might consider creating pbix file that will contain some sample data, upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.