Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rolf1994
Helper II
Helper II

Calculate value between two dates

Hi, i have the following tables in my data model (using directquery). I want to calculate the value in the ValueTable between the StartDate and EndDate.

ValueTable:
Date Value
25-2-2017 1
26-2-2017 1
27-2-2017 1
28-2-2017 1
1-3-2017 1
2-3-2017 1
3-3-2017 1
4-3-2017 1
5-3-2017 1 

 

PeriodTable:
StartDate EndDate
1-2-2017  28-2-2017
1-3-2017 31-3-2017

 

The outcome of the formule should look like this:

1-2-2017 - 28-2-2017 = 4

1-3-2017 - 31-3-2017 = 5

 

Could someone help me with this?

1 ACCEPTED SOLUTION

Hi @rolf1994,

 

You should be able to use the formula below to create a measure in this scenario, then show the measure on the Table/Matrix visual with PeriodTable[StartDate] and PeriodTable[EndDate] column. Smiley Happy

 

Measure = 
CALCULATE (
    SUM ( ValueTable[Value] ),
    FILTER (
        ValueTable,
        ValueTable[Date] >= MIN( PeriodTable[StartDate] )
            && ValueTable[Date] <= MAX ( PeriodTable[EndDate] )
    )
)

r1.PNG

 

Regards

View solution in original post

8 REPLIES 8
acorcos
New Member

ok, i found my way.

Its not elegant, but works.

 

First I created two measures:

TODAYLESS365B = (today()-364)-(day(today()))
- This returns like 01/09/2020
TODAYLESS365E = today()-365
- This returns like 07/09/2020
 
Then a new final measure
TTMMLASTSIS = CALCULATE(SUM(AC_TKTBYMM[COUNT]),DATESBETWEEN(AC_TKTBYMM[DATE],[TODAYLESS365B],[TODAYLESS365E]),AC_TKTBYMM[TKT_TYPE] IN {"System"})
Greg_Deckler
Super User
Super User

Column = CALCULATE(SUM(ValueTable[Value]),FILTER(ValueTable,ValueTable[Date]>PeriodTable[StartDate] && ValueTable[Date]<PeriodTable[EndDate]))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler

 

Thanks for your answer. i get the following error when i try to create the measure:

A single value for column 'StartDate' in table 'PeriodTable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result

I created a column and it was in the PeriodTable table.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I cannot use the calculate function when creating a new column in directquery mode. Is there any other way?

Hi @rolf1994,

 

You should be able to use the formula below to create a measure in this scenario, then show the measure on the Table/Matrix visual with PeriodTable[StartDate] and PeriodTable[EndDate] column. Smiley Happy

 

Measure = 
CALCULATE (
    SUM ( ValueTable[Value] ),
    FILTER (
        ValueTable,
        ValueTable[Date] >= MIN( PeriodTable[StartDate] )
            && ValueTable[Date] <= MAX ( PeriodTable[EndDate] )
    )
)

r1.PNG

 

Regards

Anonymous
Not applicable

This worked well for me thank you 🙏 @v-ljerr-msft just have one additional question. How would you write this to show the output as 0 for counts where there is no date values just yet? 

Hi @v-ljerr-msft

I have the same but opposite setup on my tables, how would you write the measure if the periods are on the values table and the and the second table is a single date point, where you wanted to get the sum of all values where the single date falls in-between the start and end dates?

 

ie.

Date Table:

Date

1/15/2018

2/15/2018

3/15/2018

 

Values Table

Start      | End        | Value

1/01/18 | 1/31/18 | 1

1/13/18 | 3/12/18 | 1

2/14/18 | 3/13/18 | 1

 

so i would be looking for a result like this:

Date       | Sum

1/15/18  | 2

2/15/18  | 2

 

Thanks

(let me know if this is too offtopic and I will start a new thread)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.