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

Need Help

4 Replies

  • 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.