Forum Discussion

Petanek333's avatar
Petanek333
Icon for Helper III rankHelper III
4 years ago
Solved

DAX Measure summing values between today and selected date

Hi,  I would greatly appreciate it if you could help me with creating a measure.  I have an import from an SQL database (Table 1) that gives me a stock quantity of various product IDs at the time o...
Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

    I suggest having a calendar table.

    Please check the below picture and the attached pbix file.

     

     

    Quantity measure: = 
IF (
    MAX ( 'Calendar'[Date] ) <= TODAY (),
    SUM ( Data[Quantity] )
        + CALCULATE (
            SUM ( 'Table One'[Change] ),
            'Table One'[Date] >= MAX ( 'Calendar'[Date] )
        )
        + CALCULATE (
            SUM ( 'Table Two'[Change] ),
            'Table Two'[Date] >= MAX ( 'Calendar'[Date] )
        )
)
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

Hi,

I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

I suggest having a calendar table.

Please check the below picture and the attached pbix file.

 

 

Quantity measure: = 
IF (
    MAX ( 'Calendar'[Date] ) <= TODAY (),
    SUM ( Data[Quantity] )
        + CALCULATE (
            SUM ( 'Table One'[Change] ),
            'Table One'[Date] >= MAX ( 'Calendar'[Date] )
        )
        + CALCULATE (
            SUM ( 'Table Two'[Change] ),
            'Table Two'[Date] >= MAX ( 'Calendar'[Date] )
        )
)
  • Petanek333's avatar
    Petanek333
    Icon for Helper III rankHelper III
    4 years ago

    Works perfectly thank you very much!