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

Calculations based on two date fields

Ok. Here's my problem. I have a table with two date fields (DateOrdered and DateInvoiced)

The DateInvoiced value typically a few weeks after the DateOrdered value (see sample below)

I'm trying to do some calculations based on both date fields. A simple example would be the SUM of sales (Price column) for last year. Using the example below, I would get $160 using DateOrdered and $40 using DateInvoiced

 

Ideally, we would like our user to pick that from a date slicer (as in the picture) and pick "Last 1 Year" for example. Then the result would be on a simple table with both sum of sales based on DateOrdered and also based on DateInvoiced (see picture)

 

Question.PNG

 

The immediate problem is that the slicer can only be linked to one of the dates on my table. 

 

I have only been able to use a LIMITED number of options (YTD, MTD, etc) using the approach described in this video: https://www.youtube.com/watch?v=I0UV7bGvu_U

 

Notice in the video how the timeframes are defined in a table called SelectMeasures. So we would need to add in that table a limited number of options (YTD, MTD, QTD, etc). Whereas by a having a single date slicer we can pick pretty much any possible timeframe (i.e. last 7 weeks, last 3 days, last 8 months, this month, next day, etc, etc, etc)

 

Any possible way this can be accomplished? I already told my manager this might not be possible, but maybe there is a clever way to do it. Any help would be truly appreciated.

 

Thanks!

 

2 REPLIES 2
Super User
Super User

Re: Calculations based on two date fields

Check out my Quick Measures here which might help you with your situation:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


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

Proud to be a Datanaut!


rrr3521 Frequent Visitor
Frequent Visitor

Re: Calculations based on two date fields

Thank you Greg! I will go ahead and see how I can use what it is demonstrated in the articles.