Forum Discussion

nmhung49's avatar
nmhung49
Icon for Helper II rankHelper II
1 year ago
Solved

Age Calculation in DAX Incorrect for Previous Months/Years

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. ...
Need Help
  • Khushidesai0109's avatar
    Khushidesai0109
    1 year ago

    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!

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi nmhung49 ,

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

Thanks.

nmhung49's avatar
nmhung49
Icon for Helper II rankHelper II
1 year ago

Anonymous 

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)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      nmhung49
      Icon for Helper II rankHelper II
      1 year ago

      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.