Covid-19 - Calender setup for Pre-Post Lockdown analysis

Hi,

I’m putting together some Dashboards to compare vehicle movements before and after lockdown for various parts of the world.

I have a lock down date of March 27th for Sth Africa. I’d like to setup a slicer for a Dashboard for before and after. Ideally to look at trends before and after most likely on a percentage basis.

Would this be best done as an additional column in the main Date Table and if so what how would I setup the M Code. Or should I do this using DAX instead with grouping.

How would I be able to set this up for various countries with various lockdowns dates also.
Ideally it would be good to have all the lockdown dates line up as the starting date.

Thanks in advance for any help you can provide.

Regards

Dave

Hi @DavidNealon, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Covid 19 Sample Dashboard.pbix (117.4 KB) Enterprise DNA Fleet Raw Data.xlsx (10.0 KB)

Hi All,

I’ve loaded up a Sample PBIX File with a simple measure of a 3 Day Moving Average. But I’d like to be able to build a slicer to switch between before and after the 27th of March which was the lockdown date.

Also for those that look into the PBIX file as well, I’m having trouble converting the Trip Time column to Format (“hh:mm:ss”) the column is in seconds but when I convert it to duration it sees them as days instead. I’ve left it in its raw form for you to play with.

Regards

Dave

1 Like

Nice one on this

Hi DavidNealon,

Check out the attache PBIX file that has new column Formatted_Time in hh:mm:ss format. Is this what you were looking for?

Regards
Joe

Covid 19 Sample Dashboard2.pbix (120.4 KB)

Awesome that’s great thanks Joedias

Hi Joe,

I tried to use it for calculations and I wasn’t able to use it to Sum things.

Covid 19 Sample Dashboard3.pbix (127.1 KB)

I understand it not being able to understand the formatting as a string, so I tried Sum the Total_Trip_Time column first and then convert that as a Measure using your Dax but it didn’t play nice either.

Hi Dave,

Can you explain a little bit more about what you mean when you say “I’d like to setup a slicer for a Dashboard for before and after” apart from dates what measure do you want to look at to see the trends.

Thanks
–Joe

Dave,

What type of visual are you using to display Sum Of Time.
Another question which might be something trivial that I may be missing something obvious, its about the visual that has the error it keep disappearing on and off the screen how can make it stay fixed in one spot?

Regards,
Joe

Hi Joe,

I’d like to be able to group dates prior to lock down vs after. For example I’d like to be able to show a comparison between number of trips taken before the lockdown vs the number of trips taken afterwards.

I’m looking at various types of comparisons I could do. For example I could do a percentage drop of the week before to the week after.

The other I thought of was looking at it from a Day of the week perspective as well. Say comparing the Friday before lock down compared to the Friday after.

Another idea I had was also looking at what happened after the lock down was announced (which occurred on the 16th of March) I’ve been advised anecdotally that apparently there was a lot of travel just after the announcement (people trying to get home) to a real drop off after the lockdown on the 26th of March. I really appreciate your help here Joe.

Hi Joe,

The visual I’m using to display Sum of Time is a simple card visual.

I’ve attached a sample in the Dashboard essentially I’d like to get this in “dd:hh:mm:ss” Format.

Regards

DaveCovid 19 Sample Dashboard.pbix (120.8 KB)

Hi All,

One of my first attempts is trying to create a simple calculated column in the Dates Table.

Here’s my formula

Lockdown Date = if(Dates[Date]<= 27/03/2020 ,“Before Lockdown”, “After Lockdown”)

But it doesn’t seem to understand the Logical Test at the start of the If statement and just says every date is “After Lockdown” I’m thinking I’m loading in the date of 27/03/2020 wrong somehow.

I’ve also tried to use the Lockdown data in the Bar and Linechart visual as the Column series to show which is before and after shutdown.

I’d appreciate any help you can provide, here’s my latest PBIX table.

Covid 19 Sample Dashboard.pbix (122.1 KB)

Cheers

Dave

Boom go it
Lockdown Date = if(Dates[Date]<= DATEVALUE(“27/03/2020”) ,“Before Lockdown”, “After Lockdown”)

3 posts were split to a new topic: SumOfDeaths measure and when that measure reaches 10