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

[Anonymous]

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 the pricing is based on same customer / same Item sold . The base line comparison is based on the whole last year price / unit vs which ever month is selected for the current year. Mainly because not all items are bought and purchased every month same as LY. 

In that sense which is the right date dax function I would want to go about using. Very much any comments / advices. 

SM

[amitchandak]

@Anonymous , few measures using time intelligence which, you can try

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))
last Year Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))
Average PY = IF([Average CY]=0,BLANK(),CALCULATE([Average CY],SAMEPERIODLASTYEAR("dCalendar"[Date])))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[Anonymous]

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]

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]

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