Multiple dates and cumulative totals

@Ericadyson,

I’m a bit worried about changing too many things at the same time. Let’s work for the moment within the context of your current data model, get the cumulative totals right so that you can provide the information needed ASAP, and then when the time pressure is off a bit, we can step back and rework that.

No need at the moment for the harvesting/disconnected slicer approach – I think we can make do without having to go down that road. In terms of joining the two tables, the way I would do it would be to get the structures as lined up as possible between the two tables (remove any unnecessary columns, rename columns as consistently as possible across the two tables), then for each one add a column called Status or Source (just a way to differentiate between the two once they’re combined), and in that column fill with “Isolation” for the first table, “Hospitalization” for the second table, then append (not merge - we want to create new rows, not new columns) the two tables into a new combined table.

I’ll dig in on the cumulative total work this afternoon and get back to you later today.

  • Brian

P.S. Thanks for the information about the kibbutz. I googled it, since it’s always interesting to know more about the background of the people, places and issues behind these data models and DAX measures that we work on.

Sorry, I mislead you. Definitely not going to do all this at once; I’m getting my head around things. I think I need to merge not append because the 2 tables are not the same. The hospitalization table has quite a few fields that the other table doesn’t have. If I append, I won’t get all the extra fields I need. Anyhow, let’s park that for now and concentrate on getting this model as right as I can. I only started this late last week and didn’t have time to work and prototype… so I’m paying the price for that now! I’ll work on the combining and checking things out. And of course, the extra burden is that it’s all in hieroglyphics ie Hebrew!!

You won’t lose those fields in an append. They will be pulled over for the hospitalization part of the combined table and just be given null values for the Source = Isolation records. But that’s an issue for another day… :wink:

Will get working on this after lunch, and hopefully back to you later this afternoon w/ a solution.

  • Brian

Don’t want to put you under pressure. Please don’t feel that. I think I have enough info for tomorrow. I added a visual…daily count of those going into isolation…generated from the endocrine of isolation date. You’re an enormous help Brian…so whenever you can get to this, it’ll be fine.

@Ericadyson,

Making excellent progress, but have a question: I need to calculate a Start Isolation date. I was assuming this would be 14 days prior to the End Isolation Date. Is that correct?

Thanks.

  • Brian

@Ericadyson,

Can see why this has been giving you a headache - requires some pretty advanced virtual table work. Here’s the key measure:

Count in Isol = 

VAR vTable =
CALCULATETABLE(
    ADDCOLUMNS(
        CROSSJOIN(    
            DISTINCT( 'Date'[Date] ),
            DISTINCT( Isolation[Person ID] )
        ),
        "@IsIsol", [In Isolation2] 
    ),
    ALLSELECTED( 'Date'[Date] ),
    TREATAS(
        DISTINCT( 'Date'[Date] ),
        Isolation[Date End Isolation]
    )
)

VAR vIsolCount =
CALCULATE(
    COUNTROWS( 
        FILTER(
            vTable,
            [@IsIsol] = TRUE
        )
    ),
   ALLEXCEPT(
       'Date',
       'Date'[Date]
   )
)

RETURN
vIsolCount 

I’ve gotta run now for a personal thing and haven’t had time to carefully check the numbers. Can you please run through the numbers (count and cumulative by date on the “Scratch” page of the attached file) and make sure they are correct?

I hope this is helpful. If these need tweaking, we can do so later tonight/tomorrow.

Full solution file attached below.

  • Brian

Beit Shean Corona 070-01 for brian2 - Cumulative Solution.pbix (214.7 KB)

1 Like

Wow… I would never have got to that… just shows how the model is wrong. Sorry didn’t reply but I was exhausted and bit deflated that I couldn’t solve it… went to bed to relax. Yes, in my Hebrew version I created a start date -14 from end date. Will take a look. What else can I see but mega thanks. I will lhave to use this parrot fashion (for now). Once again, this is the price of a poorly designed model, isn’t it?

@Ericadyson,

Quite honestly, this is just a difficult calculation under any model structure. There are some changes (including one I made of activating the date to isolation end date relationship, as opposed to what you had as active in date to date added relationship) that might make this incrementally easier, but if you think about all this measure is doing it’s going to be complex under any circumstances:

  1. It is taking every combination of date and person ID
  2. It is then creating a 14 day window for every pairing
  3. It is then testing whether each person on each date is within the window or outside of it (and having to create a virtual relationship to the date table since the table created in step one exists only in memory but the relationship is needed to make the calculation work)
  4. Then it is evaluating at every date and summing the unique IDs within the isolation window and reporting that out to the visual

So don’t be hard on yourself – that’s going to be a difficult calculation no matter what, and I’m not 100% certain myself that I’ve got it calculating exactly right - which is why I want you to independently validate the results rather than taking my output and assuming it’s fully problem free. Once we are absolutely certain the measure is producing completely accurate results, I will be glad to dissect it with you and work through logic of exactly what each piece is doing, so that you’re not just parroting it.

No shame whatsoever in getting stuck and asking for help – it happens to all of us, and I can point you to a bunch of my posts (including this one and this one) where I just hit a similar wall and needed the forum to get me unstuck. That’s literally what we’re here for…

  • Brian

Hi Brian

I will study your answer more fully but I don’t think we’re there yet. Because there are people moving out of isolation at the same time. So I think we need
Cumul in isolation overall - we have that. But that’s not the current picture because people are leaving isolation all the time. So I think we need the NET figure and then the cumul of that to show the moving picture.

This should be straightfoward, but it’s alluding me right now.

@Ericadyson,

Coincidentally, I just worked out the key component of that with another member earlier today. I’ll get back to you shortly with a proposed solution…

  • Brian

Hi Brian - here’s where I’ve got to. I think I have te cumulatives now due to the direct link between isolation date and date. Need to double check it so I can access history when I need to (and I will need to).

The charts with dates show just the last 2 weeks because this is an operational dashboard where social workers are contacting people daily to see if they are OK, need anything…

Anyhow, for now, this is what I have

1 Like

@Ericadyson,

See if this gets us further down the road – daily change and cumulative daily change.

Measure that does most of the heavy lifting is this one below. Everything else is pretty much straightforward branching off of this and the previous measures we developed:

Previous Isol Count = 

VAR SelRow = SELECTEDVALUE( 'Date'[Date]  )

VAR PrevDate =
CALCULATE(
    MAX('Date'[Date] ),
    FILTER(
        ALL('Date'),
        'Date'[Date] < SelRow
    )
)

VAR  Result =
CALCULATE(
   [Count in Isol],
    FILTER(
        ALL('Date'),
        'Date'[Date] = PrevDate
    )
)

RETURN
Result

I hope this is helpful. Full solution file below. I’m going to call it a night, but let me know if there’s anything else we need to do on this tomorrow. Thanks.

  • Brian

Beit Shean Corona 070-01 for brian2 - Cumulative Solution.pbix (215.9 KB)

1 Like

@Ericadyson

Just checking back to see if that got you what you needed, or if there’s more that needs to be done to get to the complete desired end state?

Thanks.

  • Brian

Epic support here.

Also many good tips for working with multiple dates in this workshop

1 Like

Thanks Sam. I missed this session and it was great. But I didn’t quite understand how the dates work when there is no relationship between the tables? I understand the USERELATIONSHIP for the orders by x date but why isn’t USERELATIONSHIP necessary for the time intelligence? It seems simpler than I thought. But why / how does it work when there is no relationship between the tables and no relationship is “turned on” as it were? Can you help me out here with my understanding? Hope you can.

Hello @Ericadyson,

The USERELATIONSHIP function works with inactive relationships to make them active in that particular measure for the calculations. If you have no relationships set up and want to create measures based on certain relationships then use the function called TREATAS. This is very similar to the USERELATIONSHIP function, but this works when we have no relationship set up and creating a virtual relationship.

Please let me know if you still face any issues.

Thanks
Shubham

Hi @Ericadyson, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi @Brian and @skathuria… Many thanks for all your very practical - hands-on help. I’m sorted (for now) and you helped me get there. I wouldn’t have managed it on my own… I felt I was going round in circles… well I WAS going round in circles. The support forum is a truly great thing. I need to beef up my use of TREATAS, but for now, I think I’m OK.

I did have one other question actually … how can I format my main date in the date table to be truncated… ie just dd/mm or mm/yy? I feel I need that when there are many dates on the x axis. I mean the date field that is the primary field for time intelligence. It must be simple, but it’s alluding me at the moment.

Hi @Ericadyson,
Type in the custom format you want here in the below-hightlighted section of the below image or select from the predefined formats as well.

I am glad that I was able to address and sort your issues. Happy to help!

Thanks
Shubham

Oh… I didn’t realise you could type in a format. I thought I could only have the ones in the presets! Brilliant. A silly thing, but it’s been bugging me. Again… thanks a lot.

1 Like