On this page you will find the specifications regarding the Model: Cash Planning and Fact Table: CASH PLAN MEASURES.
| Attribute | Description (where applicable) |
Aggregation of measures is based on SUM if not explicitly noted.
This section describes the basic measures in the cube. These are either measures defined in the core Information Source, in the model itself or in the ETL process. The columns have the following meaning:
| Measure | Calculated Y/N | Contains Time Intelligence Y/N | Definition/Note (where applicable) | Aggregation Type | Hidden Y/N | Measure Expression |
| Cash Flow (Sum By All Dates) | Y | Y | Cumulative Cash flow measure selected with switch function - Presents cumulative values even for dates without any transactions. | N | VAR MaxDate = MAX('CASH FLOW PERIOD'[Cash Flow Date]) RETURN CALCULATE( [Cash Flow], 'CASH FLOW PERIOD'[Cash Flow Date]<=MaxDate, ALL('CASH FLOW PERIOD') ) // Cumulative Cash flow by all dates. // NOTE: Cumulative value at all dates, even presents values on dates without any transactions. // NOTE: The cumulative value is the total sum from the first transaction for the cash plan. So if filtered on e.g. a specific month this measures does not start summering from the start of the month, but from the first existing transaction date. // NOTE: Do not use this measure when presenting values by Time buckets. // NOTE: Due to performance issue this measure may require that the user filter the presentation to a small selection of dates. | |
| Inflow (Sum By All Dates) | Y | Y | Cumulative Inflow measure selected with switch function - Presents cumulative values even for dates without any transactions. | N | VAR MaxDate = MAX('CASH FLOW PERIOD'[Cash Flow Date]) RETURN CALCULATE( [Inflow], 'CASH FLOW PERIOD'[Cash Flow Date]<=MaxDate, ALL('CASH FLOW PERIOD') ) // Cumulative Inflow by all dates. // NOTE: Cumulative value at all dates, even presents values on dates without any transactions. // NOTE: The cumulative value is the total sum from the first transaction for the cash plan. So if filtered on e.g. a specific month this measures does not start summering from the start of the month, but from the first existing transaction date. // NOTE: Do not use this measure when presenting values by Time buckets. // NOTE: Due to performance issue this measure may require that the user filter the presentation to a small selection of dates. | |
| Outflow (Sum By All Dates) | Y | Y | Cumulative Outflow measure selected with switch function - Presents cumulative values even for dates without any transactions. | N | VAR MaxDate = MAX('CASH FLOW PERIOD'[Cash Flow Date]) RETURN CALCULATE( [Outflow], 'CASH FLOW PERIOD'[Cash Flow Date]<=MaxDate, ALL('CASH FLOW PERIOD') ) // Cumulative Outflow by all dates. // NOTE: Cumulative value at all dates, even presents values on dates without any transactions. // NOTE: The cumulative value is the total sum from the first transaction for the cash plan. So if filtered on e.g. a specific month this measures does not start summering from the start of the month, but from the first existing transaction date. // NOTE: Do not use this measure when presenting values by Time buckets. // NOTE: Due to performance issue this measure may require that the user filter the presentation to a small selection of dates. | |
| Cash Flow (Sum By All Time buckets) | Y | Y | Cumulative Cash flow measure selected with switch function - Presents cumulative values even for time bucket without any transactions. | N | VAR CurrentTimeBucket = SELECTEDVALUE('TIME BUCKET'[Time Bucket Code]) RETURN CALCULATE( [Cash Flow], 'TIME BUCKET'[Time Bucket Code] <= CurrentTimeBucket, ALL('TIME BUCKET') ) // Cumulative Cash flow by all time buckets. // NOTE: Cumulative value at all time buckets, even presents values on time buckets without any transactions. // NOTE: Do not use this measure when presenting values by any date column. // NOTE: The cumulative value is the total sum from the first transaction for the cash plan. So if filtered on e.g. a specific date this measures does not start summering from that date, but from the first existing transaction date. // PREREQUISITE: 'Time Bucket Code' columns needs to be a whole number and with numbers increasing by time. So e.g. 'Time Bucket Desc'='Week 1' requires a lower value in 'Time Bucket Code' then 'Time Bucket Desc' = 'Week 2' etc. // PREREQUISITE: Filter a single 'Time Scale ID' value in the TIME BUCKET table if more than one Time Scale value exist. Doing such filtering without also including 'Time Scale ID' = (Blank) means that any transactions that missing time bucket value will be excluded from the calculation of the measures stored in the folder 'Base Measure' but included in this cumulative measure. | |
| Inflow (Sum By All Time buckets) | Y | Y | Cumulative Inflow measure selected with switch function - Presents cumulative values even for time bucket without any transactions. | N | VAR CurrentTimeBucket = SELECTEDVALUE('TIME BUCKET'[Time Bucket Code]) RETURN CALCULATE( [Inflow], 'TIME BUCKET'[Time Bucket Code] <= CurrentTimeBucket, ALL('TIME BUCKET') ) // Cumulative Inflow by all time buckets // NOTE: Cumulative value at all time buckets, even presents values on time buckets without any transactions. // NOTE: Do not use this measure when presenting values by any date column. // NOTE: The cumulative value is the total sum from the first transaction for the cash plan. So if filtered on e.g. a specific date this measures does not start summering from that date, but from the first existing transaction date. // PREREQUISITE: 'Time Bucket Code' columns needs to be a whole number and with numbers increasing by time. So e.g. 'Time Bucket Desc'='Week 1' requires a lower value in 'Time Bucket Code' then 'Time Bucket Desc' = 'Week 2' etc. // PREREQUISITE: Filter a single 'Time Scale ID' value in the TIME BUCKET table if more than one Time Scale value exist. Doing such filtering without also including 'Time Scale ID' = (Blank) means that any transactions that missing time bucket value will be excluded from the calculation of the measures stored in the folder 'Base Measure' but included in this cumulative measure. | |
| Outflow (Sum By All Time buckets) | Y | Y | Cumulative Outflow measure selected with switch function - Presents cumulative values even for time bucket without any transactions. | N | VAR CurrentTimeBucket = SELECTEDVALUE('TIME BUCKET'[Time Bucket Code]) RETURN CALCULATE( [Outflow], 'TIME BUCKET'[Time Bucket Code] <= CurrentTimeBucket, ALL('TIME BUCKET') ) // Cumulative Outflow by all time buckets. // NOTE: Cumulative value at all time buckets, even presents values on time buckets without any transactions. // NOTE: Do not use this measure when presenting values by any date column. // NOTE: The cumulative value is the total sum from the first transaction for the cash plan. So if filtered on e.g. a specific date this measures does not start summering from that date, but from the first existing transaction date. // PREREQUISITE: 'Time Bucket Code' columns needs to be a whole number and with numbers increasing by time. So e.g. 'Time Bucket Desc'='Week 1' requires a lower value in 'Time Bucket Code' then 'Time Bucket Desc' = 'Week 2' etc. // PREREQUISITE: Filter a single 'Time Scale ID' value in the TIME BUCKET table if more than one Time Scale value exist. Doing such filtering without also including 'Time Scale ID' = (Blank) means that any transactions that missing time bucket value will be excluded from the calculation of the measures stored in the folder 'Base Measure' but included in this cumulative measure. | |
| Cash Flow (Sum By Date) | Y | Y | Cumulative Cash flow measure selected with switch function - Presents cumulative values only for dates with transactions. | N | VAR RunningTotal= CALCULATE( [Cash flow], FILTER(ALL('CASH FLOW PERIOD'), 'CASH FLOW PERIOD'[Cash Flow Date]<=MAX('CASH FLOW PERIOD'[Cash Flow Date]))) RETURN IF(COUNTROWS('CASH PLAN DETAILS'), RunningTotal) // Cumulative Cash Flow on dates with transaction. // NOTE: Cumulative value, only presents values on dates with any transaction. // NOTE: The cumulative value is the total sum from the first transaction for the cash plan. So if filtered on e.g. a specific month this measures does not start summering from the start of the month, but from the first existing transaction date. // NOTE: Do not use this measure when presenting values by Time buckets. // NOTE: Due to performance issue this measure may require that the user filter the presentation to a small selection of dates. | |
| Inflow (Sum By Date) | Y | Y | Cumulative Inflow measure selected with switch function - Presents cumulative values only for dates with transactions. | N | VAR RunningTotal= CALCULATE( [Inflow], FILTER(ALL('CASH FLOW PERIOD'), 'CASH FLOW PERIOD'[Cash Flow Date]<=MAX('CASH FLOW PERIOD'[Cash Flow Date]))) RETURN IF(COUNTROWS('CASH PLAN DETAILS'), RunningTotal) // Cumulative Inflow on dates with transaction. // NOTE: Cumulative value, only presents values on dates with any transaction. // NOTE: The cumulative value is the total sum from the first transaction for the cash plan. So if filtered on e.g. a specific month this measures does not start summering from the start of the month, but from the first existing transaction date. // NOTE: Do not use this measure when presenting values by Time buckets. // NOTE: Due to performance issue this measure may require that the user filter the presentation to a small selection of dates. | |
| Outflow (Sum By Date) | Y | Y | Cumulative Outflow measure selected with switch function - Presents cumulative values only for dates with transactions. | N | VAR RunningTotal= CALCULATE( [Outflow], FILTER(ALL('CASH FLOW PERIOD'), 'CASH FLOW PERIOD'[Cash Flow Date]<=MAX('CASH FLOW PERIOD'[Cash Flow Date]))) RETURN IF(COUNTROWS('CASH PLAN DETAILS'), RunningTotal) // Cumulative Outflow on dates with transaction // NOTE: Cumulative value, only presents values on dates with any transaction. // NOTE: The cumulative value is the total sum from the first transaction for the cash plan. So if filtered on e.g. a specific month this measures does not start summering from the start of the month, but from the first existing transaction date. // NOTE: Do not use this measure when presenting values by Time buckets. // NOTE: Due to performance issue this measure may require that the user filter the presentation to a small selection of dates. | |
| Cash Flow (Sum By Time bucket) | Y | Y | Cumulative Cash flow measure selected with switch function - Presents cumulative values only for time buckets with transactions. | N | VAR RunningTotal= CALCULATE( [Cash flow], FILTER(ALL('TIME BUCKET'), 'TIME BUCKET'[Time Bucket Code]<=MAX('TIME BUCKET'[Time Bucket Code]))) RETURN IF(COUNTROWS('CASH PLAN DETAILS'), RunningTotal) // Cumulative Cash flow by time buckets with transactions. // NOTE: Do not use this measure when presenting values by any date column. // NOTE: The cumulative value is the total sum from the first time bucket for the cash plan. So if filtered on e.g. a specific time bucket this measures does not start summering from that point in time, but from the first existing transaction. // PREREQUISITE: 'Time Bucket Code' columns needs to be a whole number and with numbers increasing by time. So e.g. 'Time Bucket Desc'='Week 1' requires a lower value in 'Time Bucket Code' then 'Time Bucket Desc' = 'Week 2' etc. | |
| Inflow (Sum By Time bucket) | Y | Y | Cumulative Inflow measure selected with switch function - Presents cumulative values only for time buckets with transactions. | N | VAR RunningTotal= CALCULATE( [Inflow], FILTER(ALL('TIME BUCKET'), 'TIME BUCKET'[Time Bucket Code]<=MAX('TIME BUCKET'[Time Bucket Code]))) RETURN IF(COUNTROWS('CASH PLAN DETAILS'), RunningTotal) // Cumulative Inflow by time buckets with transactions. // NOTE: Do not use this measure when presenting values by any date column. // NOTE: The cumulative value is the total sum from the first time bucket for the cash plan. So if filtered on e.g. a specific time bucket this measures does not start summering from that point in time, but from the first existing transaction. // PREREQUISITE: 'Time Bucket Code' columns needs to be a whole number and with numbers increasing by time. So e.g. 'Time Bucket Desc'='Week 1' requires a lower value in 'Time Bucket Code' then 'Time Bucket Desc' = 'Week 2' etc. | |
| Outflow (Sum By Time bucket) | Y | Y | Cumulative Outflow measure selected with switch function - Presents cumulative values only for time buckets with transactions. | N | VAR RunningTotal= CALCULATE( [Outflow], FILTER(ALL('TIME BUCKET'), 'TIME BUCKET'[Time Bucket Code]<=MAX('TIME BUCKET'[Time Bucket Code]))) RETURN IF(COUNTROWS('CASH PLAN DETAILS'), RunningTotal) // Cumulative Outflow by time buckets with transactions. // NOTE: Do not use this measure when presenting values by any date column. // NOTE: The cumulative value is the total sum from the first time bucket for the cash plan. So if filtered on e.g. a specific time bucket this measures does not start summering from that point in time, but from the first existing transaction. // PREREQUISITE: 'Time Bucket Code' columns needs to be a whole number and with numbers increasing by time. So e.g. 'Time Bucket Desc'='Week 1' requires a lower value in 'Time Bucket Code' then 'Time Bucket Desc' = 'Week 2' etc. | |
| Selected Rep Curr 1 | Y | Y | Finds the currency code for reporting currency 1 | N | SELECTEDVALUE('CASH PLAN'[Cash Plan Reporting Currency 1]) // Contains the currency code for the Reporting currency 1. // PREREQUISITE: Need to select (filter) a single Cash plan for this measure to work. | |
| Selected Rep Curr 2 | Y | Y | Finds the currency code for reporting currency 2 | N | SELECTEDVALUE('CASH PLAN'[Cash Plan Reporting Currency 2]) // Contains the currency code for the Reporting currency 2. // PREREQUISITE: Need to select (filter) a single Cash plan for this measure to work. | |
| Selected Rep Curr 3 | Y | Y | Finds the currency code for reporting currency 3 | N | SELECTEDVALUE('CASH PLAN'[Cash Plan Reporting Currency 3]) // Contains the currency code for the Reporting currency 3. // PREREQUISITE: Need to select (filter) a single Cash plan for this measure to work. | |
| Acc Curr Amount | N | N | Cash flow in Accounting currency, excluding tax | N | SUM('CASH PLAN DETAILS'[Acc Currency Amount] ) // Accounting currency without tax. // Sum of the column 'CASH PLAN DETAILS'[Acc Currency Amount]. | |
| Acc Curr Amount With Tax | N | N | Cash flow in Accounting currency, including tax | N | SUM('CASH PLAN DETAILS'[Acc Currency Amount With Tax] ) // Accounting currency with tax. // Sum of the column 'CASH PLAN DETAILS'[Acc Currency Amount With Tax]. | |
| Rep 1 Curr Amount | N | N | Cash flow in Reporting currency 1, excluding tax | SUM | N | SUM('CASH PLAN DETAILS'[Rep Currency 1 Amount]) // Reporting currency 1 without tax. // Sum of the column 'CASH PLAN DETAILS'[Rep Currency 1 Amount]. |
| Rep 1 Curr Amount With Tax | N | N | Cash flow in Reporting currency 1, including tax | SUM | N | SUM('CASH PLAN DETAILS'[Rep Currency 1 Amount With Tax]) // Reporting currency 1 with tax. // Sum of the column 'CASH PLAN DETAILS'[Rep Currency 1 Amount With Tax]. |
| Rep 2 Curr Amount | N | N | Cash flow in Reporting currency 2, excluding tax | SUM | N | SUM('CASH PLAN DETAILS'[Rep Currency 2 Amount]) // Reporting currency 2 without tax. // Sum of the column 'CASH PLAN DETAILS'[Rep Currency 2 Amount]. |
| Rep 2 Curr Amount With Tax | N | N | Cash flow in Reporting currency 2, including tax | SUM | N | SUM('CASH PLAN DETAILS'[Rep Currency 2 Amount With Tax]) // Reporting currency 2 with tax. // Sum of the column 'CASH PLAN DETAILS'[Rep Currency 2 Amount With Tax]. |
| Rep 3 Curr Amount | N | N | Cash flow in Reporting currency 3, excluding tax | SUM | N | SUM('CASH PLAN DETAILS'[Rep Currency 3 Amount]) // Reporting currency 3 without tax. // Sum of the column 'CASH PLAN DETAILS'[Rep Currency 3 Amount]. |
| Rep 3 Curr Amount With Tax | N | N | Cash flow in Reporting currency 3, including tax | SUM | N | SUM('CASH PLAN DETAILS'[Rep Currency 3 Amount With Tax]) // Reporting currency 3 with tax. // Sum of the column 'CASH PLAN DETAILS'[Rep Currency 3 Amount With Tax]. |
| Trans Curr Amount | N | N | Cash flow in Transaction currency, excluding tax | SUM | N | SUM('CASH PLAN DETAILS'[Trans Currency Amount] ) // Transaction currency without tax. // Sum of the column 'CASH PLAN DETAILS'[Trans Currency Amount]. |
| Trans Curr Amount With Tax | N | N | Cash flow in Transaction currency, including tax | SUM | N | SUM('CASH PLAN DETAILS'[Trans Currency Amount With Tax] ) // Transaction currency with tax. // Sum of the column 'CASH PLAN DETAILS'[Trans Currency Amount With Tax]. |
| Cash Flow | Y | N | Cash flow measure selected with switch function | N | SWITCH([Selected Amount Measure], 1, [Acc Curr Amount With Tax], 2, [Acc Curr Amount], 3, [Trans Curr Amount With Tax], 4, [Trans Curr Amount], 5, [Rep 1 Curr Amount With Tax], 6, [Rep 1 Curr Amount], 7, [Rep 2 Curr Amount With Tax], 8, [Rep 2 Curr Amount], 9, [Rep 3 Curr Amount With Tax], 10, [Rep 3 Curr Amount] ) // Cash flow as sum of Opening balance, Inflow and Outflow. // This measure switch between the 10 different measures stored in the folder 'Values from Fact table'. // PREREQUISITE: Need to filter a single value from table SWITCH CURRENCY TYPE and SWITCH TAX for the measure to present any values. | |
| Inflow | Y | N | Inflow measure selected with switch function | N | CALCULATE([Cash Flow],'INFLOW OUTFLOW'[InFlow OutFlow Code]="IN") // Inflow - Only includes the transactions with Flow = IN, excluding OUT and CASH_BALANCE. // PREREQUISITE: Need to filter a single value from table SWITCH CURRENCY TYPE and SWITCH TAX for the measure to present any values. | |
| Outflow | Y | N | Outflow measure selected with switch function | N | CALCULATE([Cash Flow]*-1,'INFLOW OUTFLOW'[InFlow OutFlow Code]="OUT") // Outflow - Only includes the transactions with Flow = OUT, excluding IN and CASH_BALANCE // PREREQUISITE: Need to filter a single value from table SWITCH CURRENCY TYPE and SWITCH TAX for the measure to present any values. | |
| Currency Code for Subtitle | N | N | Give text to use in visual headers. | N | SWITCH( [Selected Currency], 3, "All values displayed in "& [Selected Rep Curr 1], 4, "All values displayed in "& [Selected Rep Curr 2], 5, "All values displayed in "& [Selected Rep Curr 3] ) // Contains text that can be used as a subtitle in visuals that present values by transaction currency. // The subtitle then shows the abbreviation for the selected reporting currency, which is then the currency in which all values are displayed. |
Time based measures are calculated in the cube with respect to other measures. The table below defines the meaning/definition of the different time measures. These measures need to be used with a specific time dimension hierarchy [REPORTING_DATE].[RepDateMFHy].
| Measure | Meaning |
| YTD |
Aggregated value for current month in current year. E.g.
for period 202010 Ytd represents sum of periods 202000-202010. |
| R12 |
For a given period, the sum all months from one year back up to the
previous month. E.g. Rolling 12 for 202010 is the sum of months 201910
- 202009 |
| PY |
Previous Year The value of the corresponding month one year back. E.g. for month 202010, the value of month 201910. |
| YTD PY |
Year to Date Previous Year Aggregated value for corresponding month one year back. E.g. for month 202010 YTD Previous Year represents sum of months 201901-201910. |
| R12 PY |
Rolling 12 (completed) Months Previous Year For a given month, first going one year back, then taking the sum of all months from one (more) year back up to the previous month. E.g. for month 202010 the sum of month 201810-201909 |
| Avg XXX Ytd | Sum XXX YTD/Count XXX YTD |
| Avg XXX R12 | Sum XXX R12/Count XXX R12 |
| XXX % YTD | Count XXX YTD/Count YYY YTD |
| XXX % R12 | Count XXX R12/Count XXX R12 |
| Model Name |
The data source for this object is defined using IFS Developer Tool. Further Transformations are done using Power BI.
Additional information can be found in the online documentation of Information Sources.
| Model Table | BI Access View | Referenced Information Source | Recommended Access Type |
| CASH PLAN MEASURES | FACT_CASH_PLAN_DETAIL_BI | FACT_CASH_PLAN_DETAIL | On Line |