Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sum if calender date is between 2 dates

Hi, 

 

I have a calendertable and a factstable with colum FROM and TO dates and a column with values.

I would like to sum the values if the calenderdates er are between FROM and TO dates.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Anonymous ,

    You can try to use following meaure formula if it suitable for your requirement:

    Measure =
CALCULATE (
    SUM ( Fact[Values] ),
    FILTER (
        ALLSELECTED ( Fact ),
        MAX ( CALENDAR[Date] ) IN CALENDAR ( Fact[From], Fact[To] )
    )
)

    If above not help, please share some sample data for test.
    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous ,

    You can try to use following meaure formula if it suitable for your requirement:

    Measure =
CALCULATE (
    SUM ( Fact[Values] ),
    FILTER (
        ALLSELECTED ( Fact ),
        MAX ( CALENDAR[Date] ) IN CALENDAR ( Fact[From], Fact[To] )
    )
)

    If above not help, please share some sample data for test.
    Regards,

    Xiaoxin Sheng

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    I am not sure I got it completely. But please try.

     

    Create one from and one to date join with the date dimension.

    Means both create date and resolve date will join with date of date dimension and then by using userelation you can choose the date to follow in a measure. Rest will be taken care of by grouping or filter

     

    Please refer example how can use userelation

    https://community.powerbi.com/t5/Desktop/DATE-RANGE-SLICERS-TO-COMPARE-NETSALES-OF-A-DATE-RANGE/td-p/42251

     

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601