Dynamic Date Headers

[Anonymous]

I created a table with dates that updates every week.

I created a straight table where I list all the fields with icons (KPI). You will see the dates are ordered as the OrderID above.

Under the advanced editor I updated the code as below.

As soon as I refresh the report and new dates are inserted, my graphs breaks because the dates doesn’t exist on the straight table.  Example, next week 20200926 will be updated to 20201003 and all the graphs will break and I need to remove the old date and add the new date. I also need to update all the KPI fields because the dates are in the measure.

Is there any other way I can do this?

[MFelix]

Hi @Anonymous ,

Are the table you make then unpivot to give column values? To what I can see  you have each date as a column on your model correct?

[Anonymous]

That is correct and the KPI's (red and green icons) calculates the difference for date 1 and 2 and the same for column 5 and 6. Its a table and not a matrix visual.

[MFelix]

The issue comes when you make the unpivot of the columns, based on the date, since the date are always updating you are getting new column headers and that is not matching your data.

Believe that the best way is using the original data and then use DAX to make the calculations for the table.

Can you share a sample of the data?

[Anonymous]

@MFelix 

Anything else you can think of?

thanks for the help thus far. 

please see more data attached, should the data not be sufficient.

Stages	dates	rates
Current	201909	1%
Current	201910	2%
Current	202008	3%
Current	202009	4%
Current	20200903	5%
Current	20201003	6%
Cycle 1	201909	30%
Cycle 1	201910	31%
Cycle 1	202008	32%
Cycle 1	202009	33%
Cycle 1	20200903	34%
Cycle 1	20201003	35%
Cycle 2	201909	40%
Cycle 2	201910	41%
Cycle 2	202008	42%
Cycle 2	202009	43%
Cycle 2	20200903	44%
Cycle 2	20201003	45%
Cycle 3	201909	50%
Cycle 3	201910	51%
Cycle 3	202008	52%
Cycle 3	202009	53%
Cycle 3	20200903	54%
Cycle 3	20201003	55%
NPL	201909	7%
NPL	201910	8%
NPL	202008	9%
NPL	202009	10%
NPL	20200903	11%
NPL	20201003	12%
Total	201909	20%
Total	201910	21%
Total	202008	22%
Total	202009	23%
Total	20200903	24%
Total	20201003	25%

[Anonymous]

Arrears Stage	KPI1	201908	KPI2	202008	202007	201909	KPI3	20200926	KPI4	20200826
Current	0	10.00%	1	1.00%	2.00%	1.00%	0	2.00%	1	1.00%
Cycle 1	0	30.00%	1	40.00%	30.00%	30.00%	0	30.00%	1	20.00%
Cycle 2	0	60.00%	1	50.00%	50.00%	50.00%	0	50.00%	1	50.00%
Cycle 3	0	80.00%	1	70.00%	50.00%	55.00%	0	60.00%	1	40.00%
NPL	1	5.00%	0	4.00%	5.00%	1.00%	1	3.00%	0	3.00%
Total	0	4.00%	1	2.00%	2.00%	4.00%	0	3.00%	1	3.00%
	201908<202008		201908>202008				20200926<20200826		20200926>20200826

[MFelix]

Hi @Anonymous ,

Is the import of the information in the first format or the second one?

[Anonymous]

@MFelix 

The first format is being used in my report.

[MFelix]

Hi @Anonymous ,

I was abble to get a working solution working with unpivot and creating a middle step to get the column names and sort the information in the I also create some measure for condittional formatting be aware that this may needs some changes.

Check PBIX file attach, and experiment with other information wiht different dates.