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 got the following data (+ a datetable), describing invoiced budgets within a month + budget left on the end of that month, by invoice. Main goal is to calculate the "budget-done" for each month.
INV | month | net | net-left | Client |
INV1 | 01/01/2020 | 200 | 150 | Client1 |
INV1 | 01/02/2020 | 50 | Client1 | |
INV1 | 01/03/2020 | 10 | Client1 | |
INV2 | 01/02/2020 | 100 | 90 | Client1 |
INV2 | 01/03/2020 | 90 | Client1 | |
INV3 | 01/03/2020 | 20 | Client1 |
The expected result are two matrix tables, one with summed up clientdata of delivered budget per month:
Client | 01/01/2020 | 01/02/2020 | 01/03/2020 | 01/04/2020 | 01/05/2020 | 01/06/2020 | Total |
Client1 | 50 | 110 | 60 | 100 | 0 | 0 | 320 |
And one with details about the invoice
INV | net |
INV1 | 200 |
INV2 | 100 |
INV3 | 20 |
I tryed a lot of stuff already, this got my the best result, but is still way off the expected result
SUM('Table'[net])
- if(
HASONEVALUE('Table'[net-left]),
SUM('Table'[net-left])
,0
)
+CALCULATE(
CALCULATE(
SUM('Table'[net-left]),ALLSELECTED(dates)),PREVIOUSMONTH(dates[Date]))
Best I got so far with this measure is a Matrix displaying the invoices correctly, but all sums are wrong:
Client | 01/01/2020 | 01/02/2020 | 01/03/2020 | 01/04/2020 | 01/05/2020 | 01/06/2020 | Total |
Client1 | 50 | 250 | 160 | 100 | 0 | 0 | 320 |
INV1 | 50 | 100 | 40 | 10 | 0 | 0 | 200 |
INV2 | 0 | 10 | 0 | 90 | 0 | 0 | -80 |
INV3 | 0 | 0 | 20 | 0 | 0 | 0 | 20 |
Total | 50 | 250 | 160 | 100 | 0 | 0 | 320 |
Thanks for your help!
Solved! Go to Solution.
Hi @123456789123456 ,
Try the following measure:
Measure =
SUM ( 'Table'[net] ) - SUM ( 'Table'[net-left] )
+ CALCULATE ( SUM ( 'Table'[net-left] ); DATEADD ( dates[Date]; -1; MONTH ) ) + 0
Check PBIx file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @123456789123456 ,
Try the following measure:
Measure =
SUM ( 'Table'[net] ) - SUM ( 'Table'[net-left] )
+ CALCULATE ( SUM ( 'Table'[net-left] ); DATEADD ( dates[Date]; -1; MONTH ) ) + 0
Check PBIx file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you for your help.
I actually had this solution at some point, but without the +0, but dropped it as it did not work with my dates table for some reason - filters wont change the visual (e.g. filtering for 2020 only -> 12 columns, instead of 2015-2025 as in my datetable), something I don't see in your pbix, need to look into that. The same issue also occurs when I use excatly your calculated datetable
Any tipps on this particular issue?
Thanks again, will mark as solved.
Hi @123456789123456 ,
Be aware that I only used a sample data with information for 2020, so my calendar is limited for that time frame. Believe that the way things are setup can work with more years, however your data mockup was pretty reduced, if you want please send out a broader sample and I can check if it works properly.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI don't speak Spanish, but I think I understand it now: the way you build the data table is different from my approach (which is actually a dataflow of data to keep all reports at exactly the same level). For now I'll just adapt to your dating solution
thanks again
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 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |