Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I created a measure in DAX (see below) to return the total anual budget ignoring filters:
Anual Budget = CALCULATE(SUM(FactBudget[BudgetValue]),ALL(DimDate[Date]))
The budget Fact contains budget values in the same granularity as the Date Dimension (DimDate). Thus, there is one value on each day.
FactBudget (Table)
Date | BudgetValue ----------------------------- 2018-01-01 | $100 2018-01-02 | $ 200 ....
Date Dimension DimDate
Date ------ 2018-01-01 2018-01-02 ...
I would like to do the same to claculate the total budget of the current month only what will be needed instead of
ALL(DimDate[Date])
?
What I need is something like Monthly Budget = CALCULATE(SUM(FactBudget[BudgetValue]),GETCURRENTMONTH(DimDate[Date])) where GETCURRENTMONTH does not exist.
What I tried but it does nto return correct results was:
Monthly Budget = CALCULATE(SUM(FactBudget[BudgetValue]),(YEAR( DimDate[Date] ) = YEAR ( TODAY()) && MONTH( DimDate[Date] ) = MONTH (TODAY ())))
Thanks!
Solved! Go to Solution.
I solved the issue in the meanwhile. Here is the solution that worked for me and what I was doing wrong:
Monthly Budget = CALCULATE(SUM(FactBudget[BudgetValue]),DATESBETWEEN(FactBudget[Date],DATE(YEAR(TODAY()), MONTH(TODAY()), 1),EOMONTH(today(),0)))
So the issue was that I was using the Date Dimension which is a rolling Date dimension to show dates only till today so I had to get the date from the original budget table.
I solved the issue in the meanwhile. Here is the solution that worked for me and what I was doing wrong:
Monthly Budget = CALCULATE(SUM(FactBudget[BudgetValue]),DATESBETWEEN(FactBudget[Date],DATE(YEAR(TODAY()), MONTH(TODAY()), 1),EOMONTH(today(),0)))
So the issue was that I was using the Date Dimension which is a rolling Date dimension to show dates only till today so I had to get the date from the original budget table.
i think what we can do is create a new column that gives the month number from the dates column by editing the query.
Then create measure as below
Monthly Budget = CALCULATE(SUM(FactBudget[BudgetValue]),FactBudget[Month]=MONTH(TODAY()))
where Month is the newly created column name
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |