Forum Discussion

Balazs_POL's avatar
Balazs_POL
Regular Visitor
6 years ago
Solved

Current year measure problem

Hello Everybody,

 

First of all I would like to tell you that I have started the learning of the PowerBI desktop application and I am a newbie user (yet). I have tried to find the solution or the source of the problem in several previous topics but unfortunately I have not found it. 

 

Our company is a factory which is producing food products. I would like to visualize the

  • current year which is not finished yet
  • and the previous years in a clustered column chart.

Tables what I am using:

  • Transaction table which consists of the daily issued invoices
  • DATE table --> INVOICE_ISSUE_DATE and calculated YEAR, MONTH columns

 

In the TRANSACTION table I have got the 

  • INVOICE_ISSUE_DATE
  • PRODUCT_NAME
  • PRODUCT_PRICE
  • QUANTITY
  • Calculated column - NET_VALUE: PRODUCT_PRICE * QUANTITY
  • Measure - TOTAL_SALE --> TOTAL_SALE = sum (NET_VALUE)

 

  • I created a new measure which is the following: 
    TOTAL_2018_NET_SALES = CALCULATE ([TOTAL_SALE]; SAMEPERIODLASTYEAR ('DATE'[INVOICE_ISSUE_DATE]))

    It gives the 2018_NET_SALES, it is fine. 

 

The problem is the following: how can I create a measure which gives back the TOTAL_2019_NET_SALES? I tried with the add function but it was not working:

  • TOTAL_2019_NET_SALES = CALCULATE([TOTAL_SALE];DATEADD('DATE'[INVOICE_ISSUE_DATE];0;YEAR)) 

I got the date until 31 Augusth and I would like to visualize it on the clustered column chart where I put the axis the MONTH and the values shows next to each other the 2018/2019 sales.

 

Hopefully you can help me in this issue. 

 

Thank you in advance.
Balazs

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

     Hi Balazs_POL ,

     

    We can try to use the following DAX to see if it meet your requirement.

     

    2019_NET_SALES = IF (
    YEAR ( SELECTEDVALUE ( 'TRANSACTION'[INVOICE_ISSUE_DATE] ) ) = YEAR ( TODAY () ),
    [TOTAL_SALE],
    BLANK ()
)

     

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ DongLi
    If this post helps, then please consider Accept it as the solution to help the other members find it more

4 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Balazs_POL ,

     

    If you just want to get the 2019 sales regardless of filter (or means this year, you can add -1 to get the previous year), you can create measure using following DAX.

     

    TOTAL_2019_NET_SALES = 
CALCULATE (
    [TOTAL_SALE],
   FILTER (
       ALL ( 'TRANSACTION' ),
        YEAR ( 'TRANSACTION'[INVOICE_ISSUE_DATE] ) = YEAR ( TODAY () )
    )
)

     

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ DongLi
    If this post helps, then please consider Accept it as the solution to help the other members find it more

    • Balazs_POL's avatar
      Balazs_POL
      Regular Visitor
      6 years ago

      Hello v-lid-msft,

       

      Thank you for your response. Exactly what I would like to do to get a measure which only sum the 2019 daily sales. I updated your file on the way how my file looks like. I put a text box exactly next to the row which is missing. :) 

       

      File is available here: Current-year-measure-problem_Balazs

       

      Thank in advance.

       

      Kind regards,

      Balazs

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

         Hi Balazs_POL ,

         

        We can try to use the following DAX to see if it meet your requirement.

         

        2019_NET_SALES = IF (
    YEAR ( SELECTEDVALUE ( 'TRANSACTION'[INVOICE_ISSUE_DATE] ) ) = YEAR ( TODAY () ),
    [TOTAL_SALE],
    BLANK ()
)

         

        BTW, pbix as attached.

         

        Best regards,

         

        Community Support Team _ DongLi
        If this post helps, then please consider Accept it as the solution to help the other members find it more