Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Sorry, I can't format the tables better in my question post 😞
@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)))
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |