Time intelligence functions from the output of a measure

[nightfall]

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

[lbendlin · Accepted 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.

[nightfall]

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).

I should go always through the easy way 🙂

Thank you again and best regards,

Vittorio