Custom Date Window Column based on 'What-if' parameters

[ecvizie]

I have some daily accounting (customer charges and cash receipts) data that I'd like to analyze using different combinations of rolling periods. For example: all amounts amounts charged and paid between the 25th and 6th for every month where I have history (all other dates in each month should be excluded). Days on or after the 25th (cutoff day) should be grouped in the next month. Days on or before the 5th (as of day) should be grouped in the current month.

I have two what-if parameters: the 'cutoff day' from the prior month (the 25th in my example) and the 'as of day' for the current month (the 5th in my example).

I created a date table with a relationship to the record date of each charge and created a calculated column that classifies the date accordingly using the following formula:

NewMonth =   SWITCH(TRUE()
        ,DAY(Date[Date])<='As Of Day'[As Of Day Value], Date[Date]-DAY(Date[Date])+1
        ,DAY(Date[Date])>='Cutoff Day'[Cutoff Day Value], EOMONTH(Date[Date],0)+1
        ,blank()
    )

The parameter settings in my example above (and all different combinations) yield some strange results on the date table:

Just in case the calculated column wasn't updating on the fly I tried grouping a matrix by NewDate-->Date:

1. This seems to imply that the day of April 2nd (2) is greater than than the cutoff day (5). What am I missing?

2. Will the calculated column update when a what-if parameter changes?

[v-diye-msft]

Hi @ecvizie 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[ecvizie]

Example File 

Thanks @v-diye-msft !

I created an example file with test data for this linked above. 

• The AcctgData table contains the charges and receipts examples by date
• The entities table is just another way to slice the data
• dates is the psuedo date table used in slicing and generating the data
• Both cutoff and as of parameters were added
• I recreated the calculated column attempt I made in dates[GroupedCalcColumn]. I know this will not work, but can use it to illustrate what I am looking for.
  
  • AcctgData[OldAvgChg] is derived using the calculated column, but as I understand it, needs to be grouped by a new measure in the summarize instead of dates[GroupedCalcColumn]
    

If there is only one date resulting from the switch statement (used to classify the dates based on the parameters) and one entity, then I'd like to sum the amounts of the receipts or charges.  Otherwise I'd like to be able to apply average, standard deviation, etc over whatever time period is shown.

For example:

• Using the example parameters of the 25th and the 5th: March 25th to April 5 for Entity 1 would result in a record that could be further summarized April 1-->Entity 1--> sum([charge])

Please let me know if I need to clarify more.

[Anonymous]

No. Calculated columns do not update on the fly. They are static and recalculated only during a data (re-)load.

Best
D

[ecvizie]

Can I create and measure to handle the date classification and group by that measure using summarize?

[Anonymous]

You can indeed create a measure that will dynamically classify your data but the classes' names you'd like to use in your slicers/tables must already be present in your model.

So, based on your selections in all the dimensions, your measure(s) must decide which records it/they want to see and then aggregate them accordingly.

Best
D