Dynamic table SUM next 6 months

[Lund]

Hi

I have my table like example:

Date	Qty
2022-11-01	22
2022-12-15	36
2023-01-24	1
2023-03-15	4
[...]	[...]
2024-01-21	4
2024-01-23	5

What i need, is a dynamic visual table with sum values next 6 months and a column with all values before actual month and a colm with valuea after 6 months like this:

All Before actual month	Dec 22	Jan 23	Feb 23	Mar 23	Apr 23	May 23	All from june 23 and after
22	36	1	0	3	0	0	9

And next month it should be

All Before actual month	Jan 23	Feb 23	Mar 23	Apr 23	May 23	Jun 23	All from july 23 and after
58	1	0	3	0	0	0	9

How can i solve that?

[Lund]

Sorry, I can't format the tables better in my question post 😞

[amitchandak]

@Lund , New column in the date table and measure you need

New column = if(eomonth([Date], 0) < eomonth(Today(),0) , "All Month Before actual", format([Date], "mmm-yyyy"))

measure = calculate(sum(Table[Value]), filter(Table, Table[Date] <= eomonth(today(),6)))

[Lund]

Yeah, not a bad soluiton. That works. 😼
But i also need a sum of all after next 6 month.

And the sorting of my column are alphabetich and not by date

My result:

[v-yiruan-msft]

Hi @Lund ,

You can create a measure as below to get the next 6 months values:

Measure =
CALCULATE (
    SUM ( 'table'[value] ),
    FILTER (
        ALLSELECTED ( 'table' ),
        'table'[date] >= TODAY ()
            && 'table'[date] <= EOMONTH ( TODAY (), 6 )
            && 'table'[date] <= SELECTEDVALUE ( 'date'[date] )
    )
)

And which field you are using on the matrix Columns field option? What's the data ype of that field? Is it a Text type? You can refer the following blog to custom sort the field values...

Sort a Column with a Custom Order in Power BI

If the above one can't help you get the desired result, please provide some sample data in your table 'employ' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards