Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

DAX function for comparing Last Year whole Net Sales / Unit vs this year month Net Sales/Unit

hello friends,    Would be really helpful if you could asssit me with the my below query. Might sound simple, but still got stuck a bit.  I'm doing a Pricing report in Power BI and comparison of t...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thank you very much amit. I have a dilema related to this. Will try to explain it as much as possible to make it clear. 

So what I'm trying to do is compare this year MTD sales value sold to same customer & same item vs LY whole year sales value sold to same customer & Item. If the same item is sold to same customer both the time periods then I want to return the sales value for the current year MTD . Below is the excel logic 

 

 

Here is my DAX function for this, but I don't seem to get the same value as the excel version (which is right logic). Thought you might have some advice. 

 

Sales MTD for Customers/Items sold both Years =

VAR ItemTable =
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( Attributes[Item] ),
"Sales Two Years", CALCULATE (
IF ( AND ( [Sales LY]>0 , AND([Sales MTD]> 0,AND([Sales Qty LY] > 0,[Sales Qty MTD] > 0))), TRUE, FALSE )
)
),
ALL ( 'Date' ),DATESMTD('Date'[Day])
)

VAR Customertable =
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( Attributes[Customer] ),
"Sales Two Years Customer", CALCULATE (
IF ( AND ( [Sales LY]>0 , AND([Sales MTD]> 0,AND([Sales Qty LY] > 0,[Sales Qty MTD] > 0))), TRUE, FALSE )
)
),
ALL ( 'Date' ),DATESMTD('Date'[Day])
)

VAR CALC =
CALCULATE([Sales MTD], FILTER ( ItemTable, [Sales Two Years] = TRUE ), FILTER(Customertable, [Sales Two Years Customer] = TRUE ))
RETURN
CALC

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

 

According to your code, I know that you will create two virtual tables ItemTable and Customertable then calculate [Sales MTD] based on these tables. I think you use ALL function in virtual tables. So please add some filter in last calcualte to filter virtual tables by fact table.

Try this code.

Sales MTD for Customers/Items sold both Years =
VAR ItemTable =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( Attributes[Item] ),
            "Sales Two Years",
                CALCULATE (
                    IF (
                        AND (
                            [Sales LY] > 0,
                            AND ( [Sales MTD] > 0, AND ( [Sales Qty LY] > 0, [Sales Qty MTD] > 0 ) )
                        ),
                        TRUE,
                        FALSE
                    )
                )
        ),
        ALL ( 'Date' ),
        DATESMTD ( 'Date'[Day] )
    )
VAR Customertable =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( Attributes[Customer] ),
            "Sales Two Years Customer",
                CALCULATE (
                    IF (
                        AND (
                            [Sales LY] > 0,
                            AND ( [Sales MTD] > 0, AND ( [Sales Qty LY] > 0, [Sales Qty MTD] > 0 ) )
                        ),
                        TRUE,
                        FALSE
                    )
                )
        ),
        ALL ( 'Date' ),
        DATESMTD ( 'Date'[Day] )
    )
VAR CALC =
    CALCULATE (
        [Sales MTD],
        FILTER (
            ItemTable,
            AND ( [Sales Two Years] = TRUE, [Item] = MAX ( Attributes[Item] ) )
        ),
        FILTER (
            Customertable,
            AND (
                [Sales Two Years Customer] = TRUE,
                [Customer] = MAX ( Attributes[Customer] )
            )
        )
    )
RETURN
    CALC

Best Regards,
Rico Zhou

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks Rico, was helpful. My initial formula seems to be working. 

     

    I had one another query. Above MTD calculation (which I shared first) seems to be working and I would like to use the same formula to do the YTD calculation. Below is the current YTD calculation 

     

    Sales YTD for Customers/Items sold both Years =

    VAR ItemTable =
    CALCULATETABLE (
    ADDCOLUMNS (
    VALUES ( Attributes[Item] ),
    "Sales Two Years", CALCULATE (
    IF ( AND ( [Sales LYTD]>0 , AND([Sales YTD]> 0,AND([Sales Qty LYTD] > 0,[Sales Qty YTD] > 0))), TRUE, FALSE )
    )
    ),
    ALL ( 'Date' ),DATESYTD('Date'[Day])
    )

    VAR Customertable =
    CALCULATETABLE (
    ADDCOLUMNS (
    VALUES ( Attributes[Customer] ),
    "Sales Two Years Customer", CALCULATE (
    IF ( AND ( [Sales LYTD]>0 , AND([Sales YTD]> 0,AND([Sales Qty LYTD] > 0,[Sales Qty YTD] > 0))), TRUE, FALSE )
    )
    ),
    ALL ( 'Date' ),DATESYTD('Date'[Day])
    )

    VAR CALC =
    CALCULATE([Sales YTD], FILTER ( ItemTable, [Sales Two Years] = TRUE ), FILTER(Customertable, [Sales Two Years Customer] = TRUE ))
    RETURN
    CALC
     
    Below is what I have tried to convert it based on the MTD formula. The logic in both the MTD and YTD except the base line comparison for YTD is YTD previous year and that of MTD is previoud whole year average. So for best practice I would like to use the same type of formula. 
     
    Sales YTD for Customers/Items sold both Years_test =

    VAR table1 =
    CALCULATETABLE (
    ADDCOLUMNS (
    SUMMARIZE ( Attributes, Attributes[Customer], Attributes[Item]),
    "SalesQty", [Sales Qty YTD],
    "SalesQtyLY", [Sales Qty LYTD],
    "Sales_", [Sales YTD],
    "SalesLY", [Sales LYTD]
    ),
    ALL('Date'),DATESYTD('Date'[Day])
    )
    VAR table2 =
    ADDCOLUMNS (
    table1,
    "Include",
    IF (
    ISBLANK ( [SalesQty] ) || ISBLANK ( [SalesQtyLY] )
    || ISBLANK ( [SalesLY] )
    || ISBLANK ( [Sales_] )
    || [SalesQty] <= 0
    || [SalesQtyLY] <= 0
    || [SalesLy] <= 0
    || [Sales_] <= 0,
    FALSE,
    TRUE
    )
    )
    VAR table3 =
    ADDCOLUMNS (
    table2,
    "Result",
    IF ( [Include] = TRUE, [Sales_],0)
    )

    VAR SalesForItemCustomerBothPeriods= SUMX ( FILTER ( table3, [Include] = TRUE ), [Result] )
    RETURN
    SalesForItemCustomerBothPeriods
     
    For some reason I'm finding a bit discrepencies between these two formulas. I think there is some problem filter of All(date),DatesYTD(date[day]). Any advice would be very helpful 
     
    KR,
    Sandeep