TOTALYTD Calculation with dynamic end of month

[databot_kd]

Hi community, 

I am trying to create a dynamic TOTALYTD slicer which looks at the current month and minus 1 month instead of a hard coded end of year "30/6"

Current DAX - This accumulates the total upto end of June. 
 TOTAL YTD = TOTALYTD(CALCULATE(SUM(AMOUNT), Type = "Detail"),  'Calendar'[Date], "30/06")

Im trying to have the TOTAL YTD end at the current month (-1 month). Is this possible?

Tables:
Calendar 
Transaction List

Thanks in advance 

@Ahmedx 

[Ashish_Mathur]

Hi,

Create a Calendar Table.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  Write this measure

Measure = CALCULATE(SUM(AMOUNT),Type = "Detail",datesbetween(Calendar[date],date(year(today()),1,1),eomonth(today(),-1)))

Hope this helps.

[databot_kd]

thanks for sharing your possible solutions. 
But i havent had any success yet with those DAx's. 

Trying to write a DAX formula that rolls the sum up to a dynamic end of month date... For this month i need the Sum to stop rolling at Nov. In Jan i need it to roll up to Dec etc.

[Ahmedx · Accepted solution]

TOTAL YTD = 
VAR _ToDay = EOMONTH(TODAY(),-1)
RETURN 
IF(MAX('Calendar'[Date])<=_ToDay,
TOTALYTD(CALCULATE(SUM(AMOUNT), Type = "Detail"),  'Calendar'[Date]))

[databot_kd]

Thanks @Ahmedx that worked. You assistance was much appreciated. 

[Ahmedx]

pls try this

TOTAL YTD = 
VAR _ToDay = MONTH(EOMONTH(TODAY(),-1))
VAR _tbl = 
         {(1,2),(2,33),(3,61),(4,92),
          (5,122),(6,153),(7,183),
          (8,214),(9,245),(10,275),
           (11,306),(12,336)}
VAR _Fc = MAXX(FILTER(_tbl,[Value1]=_ToDay),[Value2])
RETURN 
CALCULATE(TOTALYTD(CALCULATE(SUM(AMOUNT), Type = "Detail"),  'Calendar'[Date], _Fc)

[databot_kd]

Hi @Ahmedx 

Thanks for your help thus far. 

I get this error when i try the above DAX

[Ahmedx]

ok? pls try this

TOTAL YTD  = 
CALCULATE (
    CALCULATE(SUM(AMOUNT), Type = "Detail"),
    VAR FirstFiscalMonth =  MONTH(EOMONTH(TODAY(),-1))
    VAR LastDay = MAX('Calendar'[Date])
    VAR LastMonth = MONTH(LastDay)
    VAR LastYear = YEAR(LastDay) - IF(LastMonth < FirstFiscalMonth, 1)
    RETURN DATESBETWEEN (
        'Calendar'[Date],
        DATE(LastYear, FirstFiscalMonth, 1),
        LastDay
    )
)

[Ahmedx]

pls try this'

TOTAL YTD = 
VAR _ToDay = EOMONTH(TODAY(),-1)
RETURN 
IF(MAX('Calendar'[Date])<=_ToDay,
TOTALYTD(CALCULATE(SUM(AMOUNT), Type = "Detail"),  'Calendar'[Date], "30/06"))

[Ahmedx]

pls try this

TOTAL YTD = 
VAR _ToDay = EOMONTH(TODAY(),-1)
RETURN
CALCULATE(TOTALYTD(CALCULATE(SUM(AMOUNT), Type = "Detail"),  'Calendar'[Date], "30/06"),'Calendar'[Date] <=_ToDay)