Forum Discussion
Ainala
4 years agoFrequent Visitor
Need Help with Current Month, Total year DAX
Hello Power Users, I'm struggling with some dax measures. I have 2 slicers from Date table ( Year and Month). I would like to create measures for Month(First Day of the month-Last Day of the mont...
- 4 years ago
Hi,
Please check the attached pbix file below.
I tried to create a sample pbix file, and the data model looks like the below.
All measures are in the attached pbix file.
Current month orders: = VAR _currentyear = MAX ( 'Calendar'[Year] ) VAR _currentmonth = MAX ( 'Calendar'[Month] ) VAR _lastdateofthemonth = CALCULATE ( MAX ( 'Calendar'[Date] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = _currentyear && 'Calendar'[Month] = _currentmonth ) ) VAR _periodtable = FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= _lastdateofthemonth && 'Calendar'[Year] = _currentyear && 'Calendar'[Month] = _currentmonth ) RETURN CALCULATE ( SUM ( Data[Order] ), _periodtable )Current year upto current month orders: = VAR _currentyear = MAX ( 'Calendar'[Year] ) VAR _currentmonth = MAX ( 'Calendar'[Month] ) VAR _lastdateofthemonth = CALCULATE ( MAX ( 'Calendar'[Date] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = _currentyear && 'Calendar'[Month] = _currentmonth ) ) VAR _periodtable = FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= _lastdateofthemonth && 'Calendar'[Year] = _currentyear ) RETURN CALCULATE ( SUM ( Data[Order] ), _periodtable )Current year all orders: = VAR _currentyear = MAX ( 'Calendar'[Year] ) VAR _lastdateoftheyear = CALCULATE ( MAX ( 'Calendar'[Date] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = _currentyear ) ) VAR _periodtable = FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= _lastdateoftheyear && 'Calendar'[Year] = _currentyear ) RETURN CALCULATE ( SUM ( Data[Order] ), _periodtable )
Ainala
4 years agoFrequent Visitor
Jihwan_Kim Thank you so much, It worked like a champ