cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Patto Frequent Visitor
Frequent Visitor

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

Can anyone offer some advice please...

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

DateNo. of People
1/7/191000
2/7/191050
3/7/191187
4/7/191040
5/7/191365
6/7/191588
 
31/7/191800

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

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

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

6 REPLIES 6
OwenAuger Super Contributor
Super Contributor

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

@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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Patto Frequent Visitor
Frequent Visitor

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

Thanks @OwenAuger 

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

Cheers

 

 

Patto Frequent Visitor
Frequent Visitor

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

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.

Patto Frequent Visitor
Frequent Visitor

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

Can anyone add anything further to this?

 

Cheers

OwenAuger Super Contributor
Super Contributor

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

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Patto Frequent Visitor
Frequent Visitor

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

Thank your for assisting @OwenAuger  - much appreciated

Cheers

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)