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 ...
Need Help
malcolms's avatar
malcolms
Frequent Visitor
9 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
9 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