CODEWITHSUNDEEP
powerbidaxtabular
Rubrix
Rubrix

Reputation: 216

How to create a measure that sums rows only where sales exists this and last year?

Hello community and DAX gurus!

I am trying to create a measure that calculates the total product sales for a specfic month only for does products that has been sold the same period last year.

Below is an illustration of what I want to achieve: enter image description here

The first thing I did was to create a measure to calculate the Sales Amount for previous year:

Sales Amount PY = 
CALCULATE(
          [Sales Amount],
          SAMEPERIODLASTYEAR(DimDate[Datekey])
         )

The second thing I did was to create a Comparable range flag as measure:

ComparableRange = IF(FactSales[Sales Amount] = BLANK() || FactSales[Sales Amount PY] = BLANK(),0,1)

Third step I created a measure to calculate the total product sales:

Total Product Sales =
CALCULATE(
          FactSales[Sales Amount],ALL(DimProduct)
         )

The final step I want to calculate the total product sales only for does products being comparable. I tried this solution but not getting it to work, it is only returning blank:

Total Product Sales Comparable =
var CompRangeTable = ADDCOLUMNS(FactSales,"@CompRange",[ComparableRange])
var FilteredTable = FILTER(CompRangeTable,[@CompRange] = 1)
return
CALCULATE(FactSales[Sales Amount],ALL(DimProduct),FilteredTable)

I also tried this solution but still getting blanks:

Total Product Sales Comparable =
var FilteredTable = FILTER(FactSales, [Sales Amount PY]*[Sales Amount]+0<>0)
return
CALCULATE([Sales Amount],ALL(DimProduct),FilteredTable)

enter image description here

I wonder if the issue is that the Comparable range flag doesn't evaluate during context in the measure and potentially only returning 0 and if that is the case how would you go about to solve this problem.

To demonstrate my problem I have used the ContosoRetailDW sample database with a simple star scheme consisting in the tables "FactSales", "DimDate" and "DimProduct"

enter image description here

Upvotes: 0

Views: 609

Answers (1)

Mik
Mik

Reputation: 2103

This expression

ADDCOLUMNS(FactSales,"@CompRange",[ComparableRange])

is equal to

CALCULATETABLE(
   ADDCOLUMNS(FactSales,"@CompRange",[ComparableRange])
   ,Calendar[CalendarMonth]=2000805
   ,DimProduct[Brand]="The Phone Company"
)

so :

1 - FactSales is cutted by context

2 - ADDCOLUMNS applies a row context to [ComparableRange] measure to EACH row.

For example you have a row with FactSales[date]="01/01/2022"; FactSales[product]="iPhone"; FactSales[customer]="Bill Gates" ; FactSales[price]=200 ; FactSales[qty]=10 Your [Sales Amount PY] in [ComparableRange] will search SAMEPERIODLASTYEAR() on a day level, for the sample it is - "01/01/2021" most probably you have no sales for customer "Bill Gates" that date, so [ComparableRange] will return you - 0

Try this one, it's not optimized, so just check if it works.

Total Product Sales Comparable=
VAR CurrentCalendarMonth = SELECTEDVALUE(Calendar[CalendarMonth])
VAR allProducts = 
        CALCULATETABLE(
            VALUES(DimProduct[ProductName])
            ,ALL() -- remove all filters and crossffilters from your data model
        )
VAR totalSalesAndCompRng = 
        ADDCOLUMNS(
            allProducts
            ,"@totalAmount
                 ,CALCULATE(
                      [Sales Amount]
                      ,Calendar[CalendarMonth] = CurrentCalendarMonth
                 )
           ,"@CompRange"
                 ,CALCULATE(
                      [ComparableRange]
                      ,Calendar[CalendarMonth] = CurrentCalendarMonth
                  )
        )
VAR onlyCompRng = 
        FILTER(
            totalSalesAndCompRng
            ,[@CompRange]=1
        )
RETURN
    SUMX(onlyCompRng,[@CompRange])

Your second measure:

Total Product Sales Comparable =
var FilteredTable = 
     FILTER(
         FactSales
         ,[Sales Amount PY]*[Sales Amount]+0<>0 -- returns false 
                                                -- the reason is the same 
                                                -- as above 
                                                -- and FilteredTable is empty
     )
RETURN
    CALCULATE(
        [Sales Amount]
        ,ALL(DimProduct)
        ,FilteredTable
    )

You can try smth like this:

Total Product Sales Comparable =

var FilteredTable = 
    FILTER(
        All(DimProduct)
        ,NOT [Sales Amount PY]*[Sales Amount]=0
    )
VAR CurrentCalendarMonth = SELECTEDVALUE(Calendar[CalendarMonth])

RETURN
    SUMX(
        FilteredTable
        ,CALCULATE(
            [Sales Amount]
            ,Calendar[CalendarMonth]=CurrentCalendarMonth 
        )
    )

Upvotes: 0

Related Questions