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 all, i am trying to create the cash flow statement from a budget.
The model has only yearly values.
This is the relevant table 'Costi'
I make up the monthly values by weighting the per-month amounts from the previous years. The weight are then stored in a calculated table 'Pesi':
I finally create the measure to allocate the costs through the year:
CALCULATE(
SUMX( 'Costi', 'Costi'[Saldo finale] * - 1 * [Stagionalita] ),
TREATAS( SUMMARIZE( 'Calendar', 'Calendar'[Year] ), 'Costi'[Anno] )
)
where [Stagionalita] is the following:
VAR MeseCorrente =
IF( ISFILTERED( 'Calendar' ), VALUES( 'Calendar'[MonthNum] ), BLANK() )
RETURN
SUMX( FILTER( 'Pesi', 'Pesi'[Mese] = MeseCorrente ), 'Pesi'[Peso] )
-----------------
Now, with this measure i allocate the monthly cost, and it seems to work fine; however the next step would be to allocate to the relevant month the cash flow, e.g. the measure allocates 500€ to january for the account n.410010, but the cash flow would occur let's say the next month.
However, given that there is no table linked to the Calendar table, i am totally lost on how to do it.
Do you have by chance any hint on that?
Thank you and best regards,
Vittorio
Solved! Go to Solution.
Your calendar table should be on day level, and it should be connected into your data model.
You are already familiar with TREATAS - you can continue to use it to transport filters between unconnected tables.
Your calendar table should be on day level, and it should be connected into your data model.
You are already familiar with TREATAS - you can continue to use it to transport filters between unconnected tables.
Hi @lbendlin,
thank you for your reply.
at the end indeed i thought it'd be much easier to go through a calculated column instead of trying to do everything through measures, as at the end of the day the values i was building should have been part of the "fact table", and thus a calculated column should be still ok.
I have therefore simply first added a calculated column to 'Pesi' that multiplies the yearly budgeted value with the weight, and finally a date column with EOMONTH((date(2022,Pesi[Mese],1)),0).
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 |
---|---|
108 | |
105 | |
87 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
96 | |
82 | |
72 |