Forum Discussion

Lund's avatar
Lund
Frequent Visitor
3 years ago

Dynamic table SUM next 6 months

Hi

I have my table like example:

 

DateQty
2022-11-0122
2022-12-1536
2023-01-241
2023-03-154
[...][...]
2024-01-214
2024-01-235

 

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 monthDec 22Jan 23Feb 23Mar 23Apr 23May 23All from june 23 and after
2236103009

 

And next month it should be

All Before actual monthJan 23Feb 23Mar 23Apr 23May 23Jun 23All from july 23 and after
581030009

 

How can i solve that?

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      Lund
      Frequent Visitor
      3 years ago

      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:

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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

  • Lund's avatar
    Lund
    Frequent Visitor
    3 years ago

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