Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter Month with last month data

i have last 8 month data.. I wish to use filter "Month&Year" from FY 2019 table and SASW total need to reflect selected month total + Last month total please find the belwo file location https://1drv.ms/u/s!Ai3rGPgy20kLwRdWKMv6sHk5oNQy?e=kxzxY1
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    I used your pbix for test.

    The [Measure] is created by you not me.

    What i created are as below

     

    To show previous month total separately and selected month separately,

    Besides these measures above, create two measures

    previous_month_total = CALCULATE(SUM(sasw[Total]),FILTER(ALL(sasw),[month_diff]=-1))

selected_month_total = CALCULATE(SUM(sasw[Total]),FILTER(ALL(sasw),[month_diff]=0))

    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.

3 Replies

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

    Hi Anonymous 

    Create a new table without relationship with any other table

    FY 2019_created_byme = FILTER(ADDCOLUMNS(CALENDARAUTO(),"month-year",FORMAT([Date],"mmmm_yyyy")),DAY([Date])=1)

    Create measures in sasw

    selected month&&year = SELECTEDVALUE('FY 2019_created_byme'[Date])

month_diff = DATEDIFF([selected month&&year],MAX(sasw[As of Date]),MONTH)

selected_month_total+previous = CALCULATE(SUM(sasw[Total]),FILTER(ALL(sasw),[month_diff]=0||[month_diff]=-1))

    add [month-year] from table "FY 2019_created_byme" in the slicer

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

       

      Thanks for your guidance, i wish to show previous month total separately and selected month separately. not sum of both. Also the measure name "Measure " which you created is not changing it's value . it should be reflect selected month total 

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

        Hi Anonymous 

        I used your pbix for test.

        The [Measure] is created by you not me.

        What i created are as below

         

        To show previous month total separately and selected month separately,

        Besides these measures above, create two measures

        previous_month_total = CALCULATE(SUM(sasw[Total]),FILTER(ALL(sasw),[month_diff]=-1))

selected_month_total = CALCULATE(SUM(sasw[Total]),FILTER(ALL(sasw),[month_diff]=0))

        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.