Current year measure problem

[Balazs_POL]

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

[v-lid-msft]

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]

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 · Accepted solution]

 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

[Balazs_POL]

Hello @v-lid-msft,

Thank you for your help, it is fine. 🙂

Balazs