cancel
Showing results for
Did you mean:
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)

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

## Re: Calculations based on two date fields

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...