Need help with Dax related to FYTD

[Anonymous]

Hi community,

I need help in DAX to calculate difference of current month & previous month values, but for July the value should be as it is(our Fiscal year is July to June). Following is the snapshot of the issue: 'Additional clients per month' is calculated as difference of current month 'Active Clients Cumulative' & previous month ''Active Clients Cumulative'.

For the month of July, I want to see July value of 'Active Clients Cumulative', not the difference from previous month value.

Following is the Dax I used:

Additional Clients per month = VAR T = CALCULATE([Active Clients FYTD],PREVIOUSMONTH(Datedim[Date]))

RETURN  IF(max(Datedim[Date])<=TODAY()+30,CALCULATE([Active Clients FYTD]-T),BLANK())

Please advise,

Thanks,

Meena

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, you may try using the following formula.

Additional Clients per month =
VAR T =
    CALCULATE ( [Active Clients FYTD], PREVIOUSMONTH ( Datedim[Date] ) )
RETURN
    IF (
        MAX ( Datedim[Date] )
            <= TODAY () + 30,
        IF (
            MONTH ( MAX ( DateDim[Date] ) ) = 7,
            [Active Clients FYTD],
            CALCULATE ( [Active Clients FYTD] - T )
        ),
        BLANK ()
    )

Best Regards

Allan

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

[amitchandak]

I think Active clients should always be cumulative?

something like this with the calendar

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))

Cumm last month Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,dateadd(sales[date],-1,month) <=maxx(date,date[date])))

[Anonymous]

@amitchandak thanks for replying but it doesnt' solve my problem. I already have cumulative active clients in the column 'Active Clients Cumulative(FYTD Jul-June). If you can tweak the DAX that I provided, it might help me resolving the issue.

[amitchandak]

You can cumulative FYTD, Only for change % make it complete cumulative. Means in change % take diff of overall cumulative - cumulative till last year

[Anonymous]

Sorry, I don't get how to apply this in my situation..