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

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