Forum Discussion

malcolms's avatar
malcolms
Frequent Visitor
9 years ago
Solved

Sum Dollar value between 2 dates

Hi,

 

I wanted to sum the dollar values between 2 dates. The print shot of my data model is attached. I could achieve part of this by implementing the method used in the below post. the problem is I cannot filter data based on the Area slicer in the FactDollar Table.

 

To give you more insight to my problem. The Fact Dollar table contains area wise data of my contractual employees and the start and end month they were involved. I'm able to get the information for the amount spent each month for all of them by using the post below but if I wish to break it up by the specific Area I'm unable to do so. Tried various methods but none of them worked.

Kindly Help.

 

 Data Model. Date column in Dates table is created by the Calendar Auto Dax Function. and Dollar is is the cumulative sum function in the post link

when I select a particular area in the slicer there is no Impact on the chart below.

https://community.powerbi.com/t5/Desktop/Calculate-value-between-2-dates/m-p/156792#M67923

 

Need Help

9 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi malcolms,

    When you select Area, there is no impact on the chart. That is because when you create a calculated column Date[Dollar], you get all areas dollars. And the calculated column would not be changed by slicer, you can review this knowledage base for more details.

    So for your requirement, you need to get different area's dollars. I create the following sample table named 'Test'.



    Then create a date table using the formula.

    Date = CALENDAR(MIN(Test[Start Date]),MAX(Test[End Date]))


    In the Date table, create calculated column using the formulas.

    AA-Dollar = CALCULATE(SUM(Test[Dollar]),
    FILTER(Test,
        Test[Start Date]<='Date'[Date]&&Test[End Date]>='Date'[Date]&&Test[Area]="AA"
    )
)

BB-Dollar = CALCULATE(SUM(Test[Dollar]),
    FILTER(Test,
        Test[Start Date]<='Date'[Date]&&Test[End Date]>='Date'[Date]&&Test[Area]="BB"
    )
)

CC-Dollar = CALCULATE(SUM(Test[Dollar]),
    FILTER(Test,
        Test[Start Date]<='Date'[Date]&&Test[End Date]>='Date'[Date]&&Test[Area]="CC"
    )
)

DD-Dollar = CALCULATE(SUM(Test[Dollar]),
    FILTER(Test,
        Test[Start Date]<='Date'[Date]&&Test[End Date]>='Date'[Date]&&Test[Area]="DD"
    )
)


    Please note, I think we should use the sum function(in bold) rather than max in the post. For example, the dollar from 2017/4/1 to 2017/4/10 is 10, another record dollar 2017/4/5 to 2017/4/10 is 20, the total dollar should be (10+20) during 2017/4/5-2017/4/10, rather than the max value 20.

    Finally, you need to create a measure to get the corresponding total dollar based on the selected area value. 

    Total-dollar = SWITCH(SELECTEDVALUE(Test[Area]),
    "AA",SUM('Date'[AA-Dollar]),
    "BB",SUM('Date'[BB-Dollar]),
    "CC",SUM('Date'[CC-Dollar]),
    "DD",SUM('Date'[DD-Dollar])
)


    Create a chart, select the data as x-axis, the measure as value, then you select different in slicer, there is impact on the chart. Please see the screenshot, and you can download the attachment to test.
    Select Area "AA"Select Area "CC"

    Best Regards,
    Angelia

    • malcolms's avatar
      malcolms
      Frequent Visitor
      8 years ago
      Thanks, This approach is good when I have limited number of values in the area field. But in my case there is possibility of the number of areas increasing in the future and I want the calculation to provision for it automatically. Also if I select no value in the slicer the total field is blank. Can we have an approach where no matter how many values or dimension are being added to the fact table it dynamically give me the sum like it gives me in the below calculation I use to count the number of employees between a time period. Count of Active Vacancies = VAR currentDate = MAX ( 'Dates'[Date] ) RETURN CALCULATE ( COUNTROWS ( Vacancy ), FILTER ( Vacancy, ( Vacancy[Vacancy Start]<= currentDate && Vacancy[Filled] >= currentDate ))
      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        Hi malcolms,

        I thought the approch using VAR before, but the current data is dynamic, and it seems to compare the mutiple currents dates to mutiple start/end date. It always returns error message. So that I post the solution above, I will post update if I find another better solution.

        Best Regards,
        Angelia