Age Calculation in DAX Incorrect for Previous Months/Years

[nmhung49]

I have created a measure to calculate age_week, but it only returns the correct result based on the current day (TODAY()). When I select a previous month or year, the calculation becomes incorrect.

How can I modify my measure to return the correct result based on the selected time period? Should I create a helper table or use another approach to fix this issue?

I would appreciate your help in editing my measure to ensure accurate results.

Thank you very much! Please see the attached file 

[Khushidesai0109 · Accepted solution]

Hiiii @nmhung49 

Age_Week =
VAR SelectedDate = MAX('Date'[Date]) -- Get the latest date in the selected time period
VAR StartDate = SELECTEDVALUE('YourTable'[Start_Date]) -- Replace with your actual date column
VAR AgeInDays = DATEDIFF(StartDate, SelectedDate, DAY)
VAR AgeInWeeks = DIVIDE(AgeInDays, 7)

RETURN AgeInWeeks

If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!

[Moana]

Hi @nmhung49 ,

Can you paste your measures here?
Seems like I was unable to open the files.

Thanks.

[nmhung49]

@Moana 

Could you please download the attached file so you can see the details more clearly? The file needs to be opened using the latest version of Power BI Desktop. Thank you for your help!

This is a measure written in a calculated column:

Age_Week = 
    VAR _yearBW = LEFT(factBreeder[BIRTH_WEEK],2)
    VAR _weekBW = RIGHT(factBreeder[BIRTH_WEEK],2)
    VAR _yearCurrent = RIGHT(YEAR(TODAY()),2)
    VAR _weekCurrent = WEEKNUM(TODAY())   
    RETURN (_yearCurrent - _yearBW) * 52 + (_weekCurrent - _weekBW)

And this is a measure used in a card visual:

Age_WeekOver25 = CALCULATE([Qty_InV2], factBreeder[Age_Week] > 25)

[Moana]

Hi @nmhung49 ,

It seems like there was some problem on DAX measure. The year/month slicer at the top is from the Calendar table and I noticed that the measure Qty_InV1 references Calendar table.
This measures uses ALL (Calendar) which will ignore the slicer and return all values.

You can instead use-

and then try filtering. You will get correct values.

Hope this helps!!

[nmhung49]

I have tried it, but it didn’t return the correct values. Thank you for your suggestion! Do you have any other ideas for this case? Any insights from others would also be greatly appreciated!

Thank you for your help.