Forum Discussion
PREVIOUSMONTH Query
As you described, you want to achieve the same function as timestamp, which will not be possible in power bi. You can also understand the reason when you use power bi. It is a data display tool and does not provide data snapshot function. Since your status may be updated at any time, but the calculation logic of DAX is to filter out the data that meets your filter in the data you import, and then perform an aggregation.
So, if you want to achieve the timestamp function like Oracle database, then you can manually record it in excel in the monthly email subscription you mentioned. The report provides a "month-end" snapshot of our transaction volume and total at 2359 o'clock on the last day of each month.hackcrr
If I have answered your question, please mark my reply as solution and kudos to this post, thank you!
Thanks - well its calculating something other than a BLANK now!
The figure I'm getting is ~300 but the correct figure should be ~1,100.
As per the measure you've provided, I'm trying to calculate the number of Cases that were over 1095 days old a month ago. The live Dynamics system that I'm connected to doesn't timestamp or provide snapshots of data at a point in time - so is it even possible to use my Date2 calendar -1 MONTH to calculate how many cases were >1095 days old last month?
Cases Created on date has a many to one to Date2 Date.
Hi ArchStanton - Can you try the below modified measure. This ensures you’re looking at the cases as of the last day of the previous month. hope this works. still issue exist, please share sample data for reference.
Thank you
# Cases 3 Yrs or Older PM =
VAR EndOfPrevMonth = EOMONTH(MAX(Date2[Date]), -1)
RETURN
CALCULATE(
COUNTROWS(
FILTER(
'Cases',
'Cases'[Created On] <= EndOfPrevMonth - 1095 &&
(
('Cases'[statecode] = "Active" &&
DATEDIFF('Cases'[Created On], EndOfPrevMonth, DAY) > 1095)
||
('Cases'[statecode] <> "Active" &&
'Cases'[Resolution Date] <= EndOfPrevMonth &&
DATEDIFF('Cases'[Created On], 'Cases'[Resolution Date], DAY) > 1095)
)
)
)
)
Hi,
Thank you again for trying to provide a solution to my problem! Your code now produces a number that is over a 1000 higher than it should be.
As I've said in my reply to hackcrr above, the more I think about this requirement the more I'm led to believe that its simply not possible as Cases that are active can close at any time and others that are closed can sometimes re-open.
Maybe the only way to capture this number is to manually record it in excel from the monthly emailed subscription I have set up on this report that provides a 'month-end' snapshot of our volumnes and totals at 2359hrs on the last day of every month.
If our Dynamics system had a timestamp function like an Oracle database has then this would be straightforward.What do you think?
ThanksAs you described, you want to achieve the same function as timestamp, which will not be possible in power bi. You can also understand the reason when you use power bi. It is a data display tool and does not provide data snapshot function. Since your status may be updated at any time, but the calculation logic of DAX is to filter out the data that meets your filter in the data you import, and then perform an aggregation.
So, if you want to achieve the timestamp function like Oracle database, then you can manually record it in excel in the monthly email subscription you mentioned. The report provides a "month-end" snapshot of our transaction volume and total at 2359 o'clock on the last day of each month.hackcrr
If I have answered your question, please mark my reply as solution and kudos to this post, thank you!