transfer filter

[jvandyck]

I have a very simple report. One time dimension is linked to the fact table and one time dimension is not. I would like to transfer a slicer selection based on the unconnected time dimension (YYYYMM) to the connected time dimension (max date for the unconnected YYYYMM selection) for a YTD sales metric. I hope the solution for this question will help me for more dynamic time intelligent calculations. Thanks for your help!

Model

[v-juanli-msft · Accepted solution]

Hi @jvandyck

For example, create relationships as below

add "year/month" column from "date table1" to the slicer, then create columns and measure in "fact sales" table

column

year = YEAR('fact sales'[date])

month = MONTH('fact sales'[date])

measures

seelcted_year = SELECTEDVALUE('date table1'[year])

selected_month = SELECTEDVALUE('date table1'[month])

ytd_selected =
IF (
    MAX ( [year] ) = [seelcted_year]
        && MAX ( [month] ) <= [selected_month],
    CALCULATE (
        SUM ( 'fact sales'[sales] ),
        FILTER (
            ALL ( 'fact sales' ),
            [year] = [seelcted_year]
                && [month] <= [selected_month]
                && 'fact sales'[date] <= MAX ( 'fact sales'[date] )
        )
    )
)

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jvandyck]

Thanks, this works! however I do not fully grasp the logic...can you try to explain?

Also, what would be the formula if I want to show the mtd value for the selected year?

Many thanks again!!

[v-juanli-msft]

Hi @jvandyck

Let me confirm your requirements.

For example, when you select current year from the unconnected datetable, then the report can show current year's YTD sales and previous year's YTD sales, right?

Best Regards

Maggie 

[jvandyck]

I select a YYYYMM and would like to calculate the YTD up to the max date of that YYYYMM. Thanks for your help!