Visual based on previous periods from slicer date

[Mark1982]

Hi,

Thanks for dropping by.

Got this requirement whereby I seem to end up goign in circles. Just cant figure out what the best solution approach is.

Have a report with:

1. Sales Data (quantity and value) by date

2. Date table with dates and months years

3. Relationship between both (based on date field)

4. Month slicer (month / year) on report (single selection)

Requirement is, to produce  sales quantity and value charts, leading up to the date selected in the date filter (6 Months). This should be by month / year. So i am selecting Decemeber 2021 and want to see in the chart July 2021 until and including December 2021. This will take Year Month on the Axis and sales quantity or value as values.

Now i've been playing around with SUMMARIZE and CALCULATETABLE but ended up in tears due to disability to get dates from slicer.

CALCULATETABLE
                        (
                            SUMMARIZE(ItemLedgerEntries,'Date'[YearMonthNumber],
                            "Quantity",CALCULATE( - SUM(ItemLedgerEntries[Quantity]),FILTER(ItemLedgerEntries, ItemLedgerEntries[Entry_Type] = "Sale")),
                            "Value",CALCULATE(SUM(ItemLedgerEntries[Sales_Amount_Actual]),FILTER(ItemLedgerEntries, ItemLedgerEntries[Entry_Type] = "Sale"))
                        )

)

I've created measures including past periods and other fancy bits. But I dont seem to be able to get my head around this one.

Hope you can assist me.

[amitchandak · Accepted solution]

@Mark1982 , if you select one month and want to see more than that, you need to have slicer on independent date table 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -6) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[Mark1982]

Why havent I thought about this? Thank you very much.