Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dynamic Date Headers

I created a table with dates that updates every week.

 

John_de_Kock_0-1601301567232.jpeg

 

 

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.

 

John_de_Kock_1-1601301567234.jpeg

 

 

ddsad.jpg

 

Under the advanced editor I updated the code as below.

sdasd.jpg

 

 

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?

 

8 REPLIES 8
MFelix
Super User
Super User

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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.

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix 

Anything else you can think of?

 

thanks for the help thus far. 

 

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

Stagesdatesrates
Current2019091%
Current2019102%
Current2020083%
Current2020094%
Current202009035%
Current202010036%
Cycle 120190930%
Cycle 120191031%
Cycle 120200832%
Cycle 120200933%
Cycle 12020090334%
Cycle 12020100335%
Cycle 220190940%
Cycle 220191041%
Cycle 220200842%
Cycle 220200943%
Cycle 22020090344%
Cycle 22020100345%
Cycle 320190950%
Cycle 320191051%
Cycle 320200852%
Cycle 320200953%
Cycle 32020090354%
Cycle 32020100355%
NPL2019097%
NPL2019108%
NPL2020089%
NPL20200910%
NPL2020090311%
NPL2020100312%
Total20190920%
Total20191021%
Total20200822%
Total20200923%
Total2020090324%
Total2020100325%
Anonymous
Not applicable

Arrears Stage

KPI1201908KPI2202008202007201909KPI320200926KPI420200826
Current010.00%11.00%2.00%1.00%02.00%11.00%
Cycle 1030.00%140.00%30.00%30.00%030.00%120.00%
Cycle 2060.00%150.00%50.00%50.00%050.00%150.00%
Cycle 3080.00%170.00%50.00%55.00%060.00%140.00%
NPL15.00%04.00%5.00%1.00%13.00%03.00%
Total04.00%12.00%2.00%4.00%03.00%13.00%
 201908<202008 201908>202008   20200926<20200826 20200926>20200826 

Hi @Anonymous ,

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix 

 

The first format is being used in my report.

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.

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.