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!
Hi, ArchStanton
You can try the following DAX expression:
# Cases 3 Yrs or Older PM =
VAR SelectedDate = MAX(Date2[Date])
VAR PreviousMonthDate = EOMONTH(SelectedDate, -1)
VAR ThresholdDate = PreviousMonthDate - 1095
RETURN
CALCULATE(
COUNTROWS(
FILTER(
'Cases',
'Cases'[Created On] >= ThresholdDate &&
'Cases'[Case Length] > 1095 &&
'Cases'[statecode] = "Active"
)
),
PREVIOUSMONTH(Date2[Date])
)I will use max to return the date in the date2 table, then use Emonth to return the last day of the previous month, and then calculate the day three years ago through ThresholdDate. Then filter the rows that fall within these date ranges in Filter.
hackcrr
If I have answered your question, please mark my reply as solution and kudos to this post, thank you!
Hi,
No error message but unfortynately your measure does not return any values:
This DAX depends on your current context and you need to adjust it appropriately for your situation. As you can see, it works well in the example I created because my simple dataset can run it. If your dataset is more complex, it will appear as shown in the picture.
hackcrr
If I have answered your question, please mark my reply as solution and kudos to this post, thank you!
I require a simple Card visual that has the volume for the previous month and your code doesn't produce a number. Apologies, I should have been more specific in my previous response.
I don't think my requirement is even possible to capture what the volume was at a specific point in time in our live dynamics database as I would need to factor the following:
* What the number of Cases over 3 yrs were at the time of the last system refresh at 1200am on the last day of the previous month* some cases that that were set to status 'statecode' = resolved can be re-opened: the status of such cases would since become 'active' and would be included in any calculation cecause the created date of that case would stiull be 1095 days or more, and so these would appear in any calculation we do now because I do not have a 'timestamp' view of the volumes & statuses in our system - its always dynamic and so changes hour to hour.
Does that make sense?
Unless I'm mistaken, I think I've proved that this impossible ask?
