Forum Discussion

jvandyck's avatar
jvandyck
Helper IV
7 years ago
Solved

transfer filter

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)...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

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's avatar
    jvandyck
    Helper IV
    7 years ago

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