Forum Discussion
Adding Days to a fixed Date where Days is a Calculated Measure
Hi All,
I have a confusing one. I'll try add as much detail as possible.
Our Trading weeks start on a Sunday and end on the following Saturday. I have a measure thats currently calculating how many weeks/days its going to take us to sell out of stock at the current average sale rate per month.
The issue is, I need the measured column of weeks/days to add itself to the date the week starts on to get an expected end future date. So if our week starts on the 05/05/2024, one of the measure values is saying it would take 22 weeks to sell out on a certain type of stock, so I need the date to show 05/10/2024, but then also to skip to the next sunday date as the week goes on etc.
Is there any help to which function I can use, that will add the weeks/days as a calculated measure rather than a static number? As the weeks/days number will change depending on the product filter but also to get this to appear as a date?
I have tried the DATEADD function but I keep getting a circular reference error, unless I hard key the number of days I need added into the formula.
Looking forward to seeing your responses as I have been stuck on this for days.
Hi Anonymous
Actually that slicers cannot be used in calculated columns.
Calculated columns use row context. They are populated once at data refresh, and don't update again. If you change interactions, change slicers, the underlying row context and any calculations performed at load would be unaffected.Also measure in table view can not be affected by the slicers or filters, it could be seems as static. Slicers and filters affect visuals only.
So, you should use a measure instead of the calculated column if you would like to change the calculation in a table when the slicer value changed.
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- Greg_Deckler
Community Champion
Anonymous You can try just using simple addition ( + ) operator. Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi, thank you for your reply.
Ill provide the attached - I was still trying to figure out exactly what I wanted, but I have since added a date per week to a trading week indicator sheet which has helped.
The first image shows the measure calculated Months which changes when a different condition is selected i.e. filter is added, I have added this into the date calculation, as per image 2 - this is now showing a date as a result however it is not changing when the measure changes. The first filter added a month and a half onto the 05/05/2024 but when I select a new option in the filter
Why is this not dynamic if its linking to a dynamic measure?
NEW DEADLINE DATE = IF( 'Rolling Dates'[LWTY Flag]="TY1", 'Rolling Dates'[Date Flag]+[Test Total stock], BLANK())As you can see the Months card changed but not the earliest new deadline date?
Hi Anonymous
Actually that slicers cannot be used in calculated columns.
Calculated columns use row context. They are populated once at data refresh, and don't update again. If you change interactions, change slicers, the underlying row context and any calculations performed at load would be unaffected.Also measure in table view can not be affected by the slicers or filters, it could be seems as static. Slicers and filters affect visuals only.
So, you should use a measure instead of the calculated column if you would like to change the calculation in a table when the slicer value changed.
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
