How to use date hierarchy with a daily cumulative measure from source?

[Patto]

Can anyone offer some advice please...

I have a data source that contains a date and a daily total.
For example:

Date	No. of People
1/7/19	1000
2/7/19	1050
3/7/19	1187
4/7/19	1040
5/7/19	1365
6/7/19	1588
…
31/7/19	1800

I want to be able to utilise the date hierarchy Power BI creates so my users can drill up or down on day, month or year for a graph visual. The problem is, out of the box Power BI will simply sum all daily values in a month to arrive at the "month" total.

But what I need is when a user is looking at the month level (in the case above July 2019), I need Power BI to show the value at the last day of the month, i.e. July = 1800.

Similarly when viewing at the "Year" level, I need the visual to show the value as at 31 Dec 2019.

In other words, show the value for the last day of the date hierarchy level being displayed.

Year = 31st December xxxx

Month = Last day of the month in question

Day = Every date

I hope that makes sense? Is there a way to do this without having to work out the movement daily?

Cheers

[OwenAuger]

@Patto 

If you're using the built-in date hierarchy without creating your own Date table, you can write a measure like this:

People on Last Date = 
CALCULATE ( 
    SUM ( YourTable[No. of People] ),
    LASTDATE ( YourTable[Date].[Date] )
)

This measure will apply a filter on Date equal to the last date in whatever level of the date hierarchy you have expanded to.

If you do use a separate Date table, if you mark it as a Date table, you could change YourTable[Date].[Date] to 'Date'[Date].

Side note - To handle cases where your data doesn't reach the end of a period, this would require some slight tweaking. For example, if your table contained data up to 16 December, you might want to display the 16 December value when filtering on December. Would you need to handle such cases?

Regards,

Owen

[Patto]

Hi @OwenAuger 

Thank you for the assistance it works really well.

Unfortunately I just realised that I do in fact have the scenario where I dont have the last day of every month.

Example, for June 2019 I only have the data up to the 22nd.

Is it possible to write DAX to handle that or is it best practice to handle this in Power Query?

Also, on a side question, when using the in built date hierarchy, can I force it to order differently?

I want November to be the start of my calendar and the left most position on the x-axis of a graph instead of January.

Thoughts?

Cheers, I really appreciate your assistance.

[OwenAuger · Accepted solution]

Hi again @Patto 

To make the measure return the value on the last date that appears in the fact table (within the current date filter context), you could use:

People on Last Date = 
CALCULATE ( 
    SUM ( YourTable[No. of People] ),
    LASTDATE ( SUMMARIZE ( YourTable, YourTable[Date].[Date] ) )
)

Again, YourTable[Date].[Date] could be replaced with 'Date'[Date] in the case of a separate Date table.

On the issue of month ordering, I think you're going to have to create a separate Date table, including a column like FiscalMonth (which would be 1 for November and 12 for October). Then set your month columns to "Sort By" FiscalMonth.

The SQLBI Date Template is a very comprehensive example of how a Date table can be set up including columns relating to fiscal years. Otherwise, there are numerous examples online of Date tables created with Power Query.

Regards,

Owen

[Patto]

Thank your for assisting @OwenAuger  - much appreciated

Cheers

[Patto]

Can anyone add anything further to this?

Cheers

[Patto]

Thanks @OwenAuger 

Let me try it out today and see if that works as required, I'll report back after that.

Cheers