Totals not showing for last year sales

[Anonymous]

Hi Team, 

today: 7/17

Total sales cy till last month 

total sales ly for each month till last month  = if(LASTDATE(v_Date_Year_Month[Date])> TODAY(),
BLANK(),
CALCULATE( Sum(v_FactVendorSales[SaleAmount]),
DATESBETWEEN(v_Date_Year_Month[Date],
FIRSTDATE(DATEADD(v_Date_Year_Month[Date],-1,YEAR)),
LASTDATE(DATEADD(v_Date_Year_Month[Date],-364,day))
)))

now this shows data till june, but totals arent showing up . 

Please help me how to implement this. 

[MFelix]

Hi @Anonymous ,

Measures are based on context and in your case the context is the date when you are looking at each line you are getting the last date of the month and if it's higher than today you return blank, when you get to the total row the context are all the months so the endo of the month of the last date is the current month so returns blanks.

You need to add a SUMX to your calculation in order to achieve the result something like:

total sales ly for each month till last month =
VAR Sales =
    IF (
        LASTDATE ( v_Date_Year_Month[Date] ) > TODAY (),
        BLANK (),
        CALCULATE (
            SUM ( v_FactVendorSales[SaleAmount] ),
            DATESBETWEEN (
                v_Date_Year_Month[Date],
                FIRSTDATE ( DATEADD ( v_Date_Year_Month[Date], -1, YEAR ) ),
                LASTDATE ( DATEADD ( v_Date_Year_Month[Date], -364, DAY ) )
            )
        )
    )
RETURN
    IF (
        HASONEFILTER ( v_Date_Year_Month[Date] ),
        Sales,
        SUMX ( v_Date_Year_Month, Sales )
    )

Be aware that since I don't have a full model setup this measure may not work exactly like expected, this has to be set in the context of your model.

If it doesn't work can you please share a sample mockup?

Regards,

MFelix

[d_gosbell]

I agree 100% with Felix's diagnosis of your issue. At the total level the context will be all the months so the max date will be December 31 which is greater than TODAY() so it is returning blank. But I think there is a logic error in the suggested expression as the Sales variable will only be evaluated once, so SUMX won't give the correct result.

An alternative to checking based on the date might be to check if the current context has a non-blank sales amount (again as per Felix's response it's hard to be sure if this works as expected without having access to a model to test against)

total sales ly for each month till last month =
if( ISBLANK( SUM( v_FactVendorSales[SaleAmount] ) ,
   BLANK(),
   CALCULATE( Sum(v_FactVendorSales[SaleAmount]),
      DATESBETWEEN(v_Date_Year_Month[Date],
         FIRSTDATE(DATEADD(v_Date_Year_Month[Date],-1,YEAR)),
         LASTDATE(DATEADD(v_Date_Year_Month[Date],-364,day))
      )
   )
)

[Anonymous]

@d_gosbell , @MFelix 

I agree. Sumx doesnt work. What should i do to get the sum?

Still its showing blank even if i add the formula Felix mentioned. 

[Anonymous]

Can you please help me out !! 

[d_gosbell]

So you said that Felix's formula still returns blank, but what about the one I suggested just checking if the current amount is not blank?

total sales ly for each month till last month =
if( ISBLANK( SUM( v_FactVendorSales[SaleAmount] ) ,
   BLANK(),
   CALCULATE( Sum(v_FactVendorSales[SaleAmount]),
      DATESBETWEEN(v_Date_Year_Month[Date],
         FIRSTDATE(DATEADD(v_Date_Year_Month[Date],-1,YEAR)),
         LASTDATE(DATEADD(v_Date_Year_Month[Date],-364,day))
      )
   )
)

or alternatively checking if the current amount is greater than 0

total sales ly for each month till last month =
if( SUM( v_FactVendorSales[SaleAmount] ) > 0 ,
   BLANK(),
   CALCULATE( Sum(v_FactVendorSales[SaleAmount]),
      DATESBETWEEN(v_Date_Year_Month[Date],
         FIRSTDATE(DATEADD(v_Date_Year_Month[Date],-1,YEAR)),
         LASTDATE(DATEADD(v_Date_Year_Month[Date],-364,day))
      )
   )
)