How to "As of" Normalized Date for Data Calculations

[aalamillo]

Hey everyone, 

So I'm relatively new to Power BI, and I'm trying to figure out how to make the date of normalization for graphics dynamic. For example, my slicer currently includes data normalized as of 1/1/2023. If I were to change the earlier date on the slicer to 1/1/2024, I'd like the data to become normalized as of 1/1/2024. Is there any way to do that? I assume so, but I'm not familiar enough with Power BI's syntax.

Open to any and all suggestions! Thank you in advance. 

[pborah]

@aalamillo sure. To reflect percentage change calculations based on slicer date, your calculation will also have to include that same date column you are using in the slicer. You cannot use a hard date value or a different date field.

This is on a simplified level. On a more complex level, you have to atleast use a related date field that has a direct relationship with the rest of your data. You cannot use an unrelated date field.

This is the best I can give you without fully seeing what exactly you're doing in there. 

[pborah]

Hi @aalamillo I'm not sure if you are using the correct terminology with "normalization". Can you please provide an example of your desired output with some screenshots or sample data/calculation?

[aalamillo]

Here is a picture of my dashboard. Currently, the normalized data is as of 1/1/2023, meaning the % change is for each day compared to the price at 1/1/2023. I'd like to make the normalization date dynamic, so that if I move the earlier slicer date to 1/1/2024, the graphics will calculate and show the % change in the data since 1/1/2024. 

[danextian]

Hi @aalamillo 

If you want to compare a specific date value dynamically with values from the other dates, you will need to use a disconnected dates table. In the screenshots below, Total Revenue Selected Date is calculated based on the max date of Disconnected Date slicer. The value of which is applied to all rows in the table. Even if the max Disconnected Date is outside the selected Related Date range, its value is still being returned.

Please see attached sample pbix.

[pborah]

Thanks @aalamillo by "normalize" do you mean the statistical normalization process using Z-Scores? If not, can you please explain how you are "normalizing" your data?

[aalamillo]

Hey - I guess just forget the word "normalization". Another way to put it is that I'm looking for a dynamic % change calculation based on the earlier date of the slicer. 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.