Forum Discussion

sapirmarko's avatar
sapirmarko
Helper I
2 years ago

Display Last 6 Month and Selected Month data using Date Dimension

In the Power BI report I have a Date dimension and sales fact tables. In report I have added single select monthincalender slicer that combine month& year and a line chart which should show month wise count of orders. 

 my monthincalender slicer is single select.

I want the last 6 months data along with the selected monthincalender in Month over Month chart

 

I did it that way:

 Date dimension and sales fact table are related with a date column

Measure = CALCULATE(cout(sales [sales_id]), DATESINPERIOD(‘Date’[Date], MAX(‘Date’[Date]), -6, MONTH))

 

but I still get in my chart: instead of see each month in the last 6 month data I see only for the selected month the cumulated number

note: I don't want to create new slicer table 

Need Help

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    Hi,

    I am not sure if I fully understood your question, but I tried to create a sample pbix file like below.

    Please chech the below picture and the attached pbix file.

    I hope the below can provide an idea to create a solution for your datamodel.

     

     

     

    Sales total: = 
SUM( Sales[Sales])

     

    Expected result measure: = 
VAR _selectedyearmonthend =
    MAX ( 'Slicer calendar'[Month-Year sort] )
RETURN
    CALCULATE (
        [Sales total:],
        KEEPFILTERS (
            DATESINPERIOD ( 'Calendar'[Date], _selectedyearmonthend, -6, MONTH )
        )
    )

     

    • sapirmarko's avatar
      sapirmarko
      Helper I
      2 years ago

      hi thanks for your answer but I don't want to create new table because I can't change my data model

      and I have a lot of other charts and measures that based on this slicer