Please help with creating a measure in DAX

[Anonymous]

Hello, please, help me calculate a DAX measure, that accumulates all monthly facts on first or last date of month) without splitting by days. Thank you in advance!

https://docs.google.com/spreadsheets/d/16aqAMmTDbWlDksY18xXXjlQmtxuRnuFE/edit?usp=sharing&ouid=10565...

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

First date:

sum of month1 first =
var _sumall=
SUMX(FILTER(ALL('Table'),
YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))),[tn])
var _mindate=
MINX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))),[Date])
return
IF(
    MAX('Table'[Date])= _mindate,_sumall,BLANK())

Last date:

sum of month1 last =
var _sumall=
SUMX(FILTER(ALL('Table'),
YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))),[tn])
var _maxdate=
MAXX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))),[Date])
return
IF(
    MAX('Table'[Date])= _maxdate,_sumall,BLANK())

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Greg_Deckler]

@Anonymous Maybe:

Column Sum of Month = 
  VAR __Date = [Date]
  VAR __Fruit = [Fruit]
  VAR __Year = YEAR(__Date)
  VAR __Month = MONTH(__Date)
  VAR __Table = FILTER('Table', YEAR([Date]) = __Year && MONTH([Date]) = __Month && [Fruit] = __Fruit)
  VAR __MinDate = MINX(__Table,[Date])
RETURN
  IF(__Date = __MinDate, SUMX(__Table,[tn]),BLANK())