YTD Predicted Sales but showing previous months sales

[villa1980]

Hi all,

 I have been asked to show predicted sales for Jan 25 - Previous Day. I have the calculation for this however, when calculating comparison to budget the previous months to this month still show the predicted sales calculation.

 What I would like to show is that Jan to Mar is the actual Sales and current month is the predcted sales.

 Just cannot get my head round the DAX for it.

 Thanks

 Alex

[andrewsommer]

Can you share more info so we can try to help.  Providing your current DAX and basic table structure would be a great start if you can't provide an actual file.  

[villa1980]

Yes Sure I have included YTD Prev Month Sell VAl ( Jan - Mar), 

YTD Prev Month Sell Val = VAR current_month = MONTH(TODAY()-1)

                  VAR current_year = YEAR(TODAY()-1)

                  VAR previous_month = current_month -1

                  VAR previous_year = current_year -1

                  RETURN

                  CALCULATE(

[Sales],

FILTER('ODS AAS_DATES',

    IF( previous_month = 0 , 'ODS AAS_DATES'[MONTH NUMBER] <= 12 && 'ODS AAS_DATES'[YEAR] = previous_year   && 'ODS AAS_DATES'[DATE] <= 'ODS AAS_DATES'[LastSaleDatePY],

    'ODS AAS_DATES'[MONTH NUMBER] < current_month && 'ODS AAS_DATES'[YEAR]  = current_year)

)

                  )

YTD Predicted Sales = [YTD Pred Sell Val]/[WD YTD]*[WD Year Total]

YTD Pred Sell Val = VAR current_month = MONTH(TODAY()-1)

                  VAR current_year = YEAR(TODAY()-1)

                  VAR previous_month = current_month -1

                  VAR previous_year = current_year -1

                  RETURN

                  CALCULATE(

[Sales],

FILTER('WORKING DAYS CALENDAR',

    IF( previous_month = 0 , 'WORKING DAYS CALENDAR'[Month No] <= 12 && 'WORKING DAYS CALENDAR'[Year] = previous_year  && 'WORKING DAYS CALENDAR'[Date] <= 'WORKING DAYS CALENDAR'[LastSaleDatePY],

    'WORKING DAYS CALENDAR'[Month No] <= current_month && 'WORKING DAYS CALENDAR'[Year]  = current_year && 'WORKING DAYS CALENDAR'[Date] <= 'WORKING DAYS CALENDAR'[Current Date])))

Current Month Sell Val = VAR current_month = MONTH(TODAY()-1)

                  VAR current_year = YEAR(TODAY()-1)

                  VAR previous_month = current_month -1

                  VAR previous_year = current_year -1

                  RETURN

                  CALCULATE(

[Sales],

FILTER('ODS AAS_DATES',

    IF( previous_month = 0 , 'ODS AAS_DATES'[MONTH NUMBER] = 12 && 'ODS AAS_DATES'[YEAR] = previous_year && DAY('ODS AAS_DATES'[DATE]) <= 'ODS AAS_DATES'[CD Day No],

    'ODS AAS_DATES'[MONTH NUMBER] = current_month && 'ODS AAS_DATES'[YEAR]  = current_year)

)

                  )

[villa1980 · Accepted solution]

Managed to find the solution, nested calulation with Var and here is the bad boy, feel free to use 🙂

YTD Prev Month Sell Val =

VAR prev_month_ytd =

                  CALCULATE(

[Sales],

FILTER('ODS AAS_DATES',

    IF( MONTH(TODAY()-1) = 0 , 'ODS AAS_DATES'[MONTH NUMBER] <= 12 && 'ODS AAS_DATES'[YEAR] = YEAR(TODAY()-1)-1   && 'ODS AAS_DATES'[DATE] <= 'ODS AAS_DATES'[LastSaleDatePY],

    'ODS AAS_DATES'[MONTH NUMBER] < MONTH(TODAY()-1) && 'ODS AAS_DATES'[YEAR]  = YEAR(TODAY()-1))

))

VAR cm_month =

                  CALCULATE(

[CM Predicted Sales],

FILTER('ODS AAS_DATES',

    IF( MONTH(TODAY()-1) = 0 , 'ODS AAS_DATES'[MONTH NUMBER] <= 12 && 'ODS AAS_DATES'[YEAR] = YEAR(TODAY()-1)-1   && 'ODS AAS_DATES'[DATE] <= 'ODS AAS_DATES'[LastSaleDatePY],

    'ODS AAS_DATES'[MONTH NUMBER] = MONTH(TODAY()-1) && 'ODS AAS_DATES'[YEAR]  = YEAR(TODAY()-1))

))

RETURN

If(isblank(prev_month_ytd), cm_month, prev_month_ytd)