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
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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'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