Forum Discussion
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 month), YeartoCurrentMonth( First Day of the year - Last day of the month) and TotalYear(First Day of the year - Last day of the Year).
If the user selects Year-2022 and Month-June or 6 in the slicers then i need to calculate Openorders.
| Current Month Orders | 6/1/22 - 6/30/22 |
| Current Year upto current month Orders | 1/1/22 - 6/30/22 |
| Whole Current year Orders | 1/1/22 - 12/31/22 |
Please help me with the calculation.
Thanks,
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 )
2 Replies
Jihwan_Kim Thank you so much, It worked like a champ
- Jihwan_Kim
Super User
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 )
