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

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.

4 Replies

  • 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!

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    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.

    • jvandyck's avatar
      jvandyck
      Helper IV
      7 years ago

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