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
DanyyalK
Regular Visitor

Incorrect Value with Date Calculation

Hey everyone,

 

 

I'm using PowerBI to create an aging report. I use two measures, one that calculates aging as of a given date, and another measure that calculates aging based on the last day of the last month. When I run both these measures giving the same date, the value is drastically different. Am I doing something wrong?

 

>120 Days =
VAR LastDay= MAX(KeyDate[Date]) //should return the selected date
RETURN
SUMX(
FILTER(
'Debtors Aging Open Items Total Dump (2)',
'Debtors Aging Open Items Total Dump (2)'[Net due date.Net due date Level 01]<=LastDay-121 && 'Debtors Aging Open Items Total Dump (2)'[Item Status.Item Status Level 01] = "O"
),
'Debtors Aging Open Items Total Dump (2)'[Debit/Credit Amount]
)
 
This is the measure that calculates based on the last date of last month:
 
(MON-1) >120 Days =
VAR LastDay= EOMONTH(MAX(KeyDate[Date]),-1) //should return last date of last month
RETURN
SUMX(
FILTER(
'Debtors Aging Open Items Total Dump (2)',
'Debtors Aging Open Items Total Dump (2)'[Net due date.Net due date Level 01]<=LastDay-121 && 'Debtors Aging Open Items Total Dump (2)'[Item Status.Item Status Level 01] = "O"
),
'Debtors Aging Open Items Total Dump (2)'[Debit/Credit Amount]
)
 
 
 
1 ACCEPTED SOLUTION
JarroVGIT
Resident Rockstar
Resident Rockstar

If you give them the same date, the first measure will evaluate variable LastDay as that date, while the second measure will evaluate it as the date of the end of the previous month. So if the context of KeyDate[Date] only contains 15/01/2020, then the first measure will have VAR LastDay =  15/01/2020 and the second measure will have VAR LastDay =  31-12-2019.

In another case, if the context of KeyDate[Date] only contains 30/11/2019, then the first measure will have VAR LastDay = 30/11/2019 and the second measure will have VAR LastDay =  31-10-2019. (becasue of the -1 in your VAR statement).

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





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

Proud to be a Super User!




View solution in original post

1 REPLY 1
JarroVGIT
Resident Rockstar
Resident Rockstar

If you give them the same date, the first measure will evaluate variable LastDay as that date, while the second measure will evaluate it as the date of the end of the previous month. So if the context of KeyDate[Date] only contains 15/01/2020, then the first measure will have VAR LastDay =  15/01/2020 and the second measure will have VAR LastDay =  31-12-2019.

In another case, if the context of KeyDate[Date] only contains 30/11/2019, then the first measure will have VAR LastDay = 30/11/2019 and the second measure will have VAR LastDay =  31-10-2019. (becasue of the -1 in your VAR statement).

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





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

Proud to be a Super User!




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.

Top Solution Authors