Summarizecolumn fillter

[Jacques_N]

Hello to all

I have a table, with Date; Plant; material code and qty delivered , by day and that for 3 years.

I would like to aggreagate this table by Plant/material code (agregation of qty delivered) for the 12 previous month of the date selected in my date slicer.

I tried with summarizecolumns , but I am unable to firter for the 12 previous month. 

Thanks for your help.

Jacques

[v-yingjl]

Hi @Jacques_N ,

To summarize a table based on date slicer, create a calculated table first:

summarize table =
SUMMARIZE (
    'Table',
    'Table'[Date],
    'Table'[Matrial code],
    'Table'[Plant],
    'Table'[qty delivered]
)

Then create a visual control measure and set its value as 1 in the visual filter:

control =
VAR _date =
    SELECTEDVALUE ( 'summarize table'[Date] )
VAR _d =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF (
        _date <= _d
            && _date
                >= DATE ( YEAR ( _d ) - 1, MONTH ( _d ), DAY ( _d ) ),
        1,
        0
    )

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Jacques_N seems like you are looking for rolling 12 months

Rolling Total = 
CALCULATE ( SUM ( Table[Qty] ), DATESINPERIOD ( DateTable[Date], MAX ( DateTable[Date] ), -12, MONTH )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡