PREVIOUSMONTH Query

[ArchStanton]

Hi,

Is it possible to calculate the volume of cases that are over 3 years old as it was a month ago using PREVIOUSMONTH or DATEADD?

This measure calculates the number of Cases that are 3 years or older as at NOW = 

# Cases 3 Yrs or Older = 
    CALCULATE(
        COUNTROWS(FILTER('Cases',
        'Cases'[Case Length] > 1095)),
        'Cases'[statecode] = "Active")

'Cases' [Case Length] is a calculated column = 

Case Length = 
IF (
    'Cases'[statecode] = "Active",
    DATEDIFF ( 'Cases'[Created On], NOW (), DAY ),
    DATEDIFF ( 'Cases'[Created On], 'Cases'[Resolution Date], DAY ))

I have tried the following measure but the result is blank, I have a feeling this isn't possible - can anyone help?

I've tried using the 'Created On' Date in the Cases Table and the Date2 Date that it is related to but neither works.

# Cases 3 Yrs or Older PM = 
    CALCULATE(
        [# Cases 3 Yrs or Older],
        PREVIOUSMONTH(Date2[Date]))

[hackcrr]

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!

[ArchStanton]

Hi, 

No error message but unfortynately your measure does not return any values:

[hackcrr]

@ArchStanton 

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!

[ArchStanton]

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?

[rajendraongole1]

Hi @ArchStanton - create a measure that shifts the calculation context back by one month, hope you already have a date table as per above reference.

# Cases 3 Yrs or Older PM =
CALCULATE(
COUNTROWS(
FILTER(
'Cases',
'Cases'[Case Length] > 1095
)
),
'Cases'[statecode] = "Active",
DATEADD(Date2[Date], -1, MONTH)
)

Try the above. Hope it works.

[ArchStanton]

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.

[rajendraongole1]

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)
)
)
)
)

[ArchStanton]

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?
Thanks

[hackcrr · Accepted solution]

@ArchStanton 

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!

[Ritaf1983]

Hi @ArchStanton 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[ArchStanton]

Hi,

Thanks for your reply.
I've had a possible solution from a SuperUser that produces a result other than BLANK for the first time so we may be getting somewhere at last. 
Can you have a look at my response to his solution and see if this is at all possible please?

Unfortunately I cannot share a pbix file for sommercial sensitivity reasons, the data model is huge and very complex so even producing a dummy file would take some time. If you think the other response is invalid and think its worthwhile, I'll try and create a dummy pbix file when I get some time.

Thanks,

[Ritaf1983]

Hi @ArchStanton 

Hi, I apologize, but in most cases, I really can't fully understand the request until I 'touch' the data myself. If the person who started working on it doesn't assist, I'll try to help once you attach the file.