Create summary table with qtd values (and still showing months in column)

[siemdevlieger]

Hi community!

I am struggling with creating a summary table showing me qtd values on monthly records.

I know how to do it with measures, but am really looking to have a table with these details, so I can use this table again for other things. The base table that I use contains a huge amount of columns, the summary table should look somewhat like below:

I tried to achieve it with the summarize and add column function, but I only get the sales per month. I don't succeed in getting the correct QTD values.

Someone who tried this before and might have an example formula with some explanation?

Thanks in advance!

Siem

[v-frfei-msft · Accepted solution]

Hi @siemdevlieger ,

Have a try 🙂

QTD SALES = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Year] = EARLIER ( 'Table'[Year] )
            && 'Table'[Month] <= EARLIER ( 'Table'[Month] )
            && 'Table'[Product group] = EARLIER ( 'Table'[Product group] )
            && 'Table'[Region] = EARLIER ( 'Table'[Region] )
    )
)

[siemdevlieger]

Thanks Frank!

I have reviewed the formula in your dataset and have made a small addition as it was now giving YTD sales instead of QTD sales. For that I also added the 'Quarter' column in the filter of your formula. It give the exact results then as in my initial question.

I will work tomorrow / Wednesday on implementing this in my own dataset, but have a good feeling about this solution!

Will get back to you once implemented with any issues I might encounter!

Regards,

Siem

[siemdevlieger]

Thanks again Frank,

I have implemented this part now in my Dashboard and it works exactly as it should!

It was a crucial part for some customized calculations in my Dashboard and was giving me a bad headache in the last weeks to get this solved 🙂

Best regards,

Siem