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

sum range date

HI, everyone...

 

I have have a table with dates and values, i need to calculate (column) two totals.

 total1 = current value  + sum(13 days previous of this date)

 total2 = sum(next 14 days of the previous of that date)

Example:

 Total1 for 7/1/2019 = value of 7/1/2019 + range 6/30/2019 - 6/18/2019 (13 days)

 Total2 for 7/1/2019 = range from 6/17/2019 to 6/4/2019 (next 14 days)

 

Any help

 

range_date_total.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: sum range date

Hi @SQUILES ,

 

To create measures as below.

total1 = 
VAR dat =
    DATE ( 2019, 07, 01 )
VAR last13 = dat - 13
RETURN
    CALCULATE (
        SUM ( 'Table'[vale] ),
        FILTER ( 'Table', 'Table'[date] >= last13 && 'Table'[date] <= dat )
    )
total 2 = 
VAR d =
    DATE ( 2019, 07, 01 )
VAR a = d - 14
VAR c = a - 13
RETURN
    CALCULATE (
        SUM ( 'Table'[vale] ),
        FILTER ( 'Table', 'Table'[date] <= a && 'Table'[date] >= c )
    )

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
Super User
Super User

Re: sum range date

You are going to need to use EARLIER. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Highlighted
Community Support Team
Community Support Team

Re: sum range date

Hi @SQUILES ,

 

To create measures as below.

total1 = 
VAR dat =
    DATE ( 2019, 07, 01 )
VAR last13 = dat - 13
RETURN
    CALCULATE (
        SUM ( 'Table'[vale] ),
        FILTER ( 'Table', 'Table'[date] >= last13 && 'Table'[date] <= dat )
    )
total 2 = 
VAR d =
    DATE ( 2019, 07, 01 )
VAR a = d - 14
VAR c = a - 13
RETURN
    CALCULATE (
        SUM ( 'Table'[vale] ),
        FILTER ( 'Table', 'Table'[date] <= a && 'Table'[date] >= c )
    )

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

SQUILES Frequent Visitor
Frequent Visitor

Re: sum range date

@v-frfei-msft  thanks, very much....

 

The Dax work excellent!!! very grateful!!! thanks again.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,611)