I attached pbix sample file with my case - this is not my source file, but refers to my main problem with measures.
How do you handle measures for tables that contain aggregate results. I am referring to the table template in the attached pbix file. At the moment, for each new data output - one access table that is created weekly, I am forced to make a copy of the measure in PBI with the changed date of the report.
I am thinking about measure like: Total sum arrears on 2020-03-02, Total sum arrears on 2020-03-09, Arrears 11 Week = CALCULATE([Arrears];'Measure'[Data OB]= DATE(2020;03;09)) Total sum arrears 2020-03-16 Arrears 12 Week = CALCULATE([Arrears];'Measure'[Data OB]= DATE(2020;03;16)) Diff. week to week = [Arrears 11 Week] - [Arrears 12 Week]
In new week I have to create a new measure and change source of charts/visualisations after refreshing source data, despite the fact that main table does not change and only new rows are created for the new period - new week.
I need to to change source of measures on current week, last week,etc.
Added information about arrear/sales to fact table are not always the same - new parts appear, while others can be reduced. I would like to be able to take full advantage of the time intelligence functions and reduce the need to create the same measures for new report dates. I will be grateful for any suggestions.
I was thinking about a solution with the datebetween function and use of two slicers, only two measures(current week, last week) that will allow me to choose two periods that should be compared with each other and shown as a difference as change of arrears values week to week.Arrears.pbix (1.3 MB)
Thank you in advance,