How to calculate sales from specify year

[mdaamirkhan]

Hi All,

I need help on dax How to calculate sales from specify year  as I have multiple year. I want to  calculate sales from 2021 1st Jan till 2025 or so on

[PhilipTreacy · Accepted solution]

Hi @mdaamirkhan 

Download sample PBIX file

Your question isn't very clear and you have only provided a column of dates, no other data.

Are you trying to calculate sales for a selected year e.g. Sales for 2019, Sales for 2020 etc?

If so, and if you have data like this,

then you just need a slicer showing the years in your dates and this measure

Sales for Year = IF(ISFILTERED('Table'[Date].[Year]),CALCULATE(SUM('Table'[Sales]), FILTER(ALL('Table'), YEAR('Table'[Date]) = YEAR(MAX('Table'[Date])))),"")

This isn't using a DateTable.  If you have a dateTable the measure will be different.  Please provide more information and an exampleof your desired result.

regards

Phil

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if you need to figure it out like below, but please correct me if I were wrong.
Please check the below DAX measure, picture, and link.

Value total from year2021 =

VAR fromwhatyear = 2021

VAR newtable =

CALCULATETABLE (

SUMMARIZE (

ALLSELECTED ( Data ),

Data[date],

"@valuetotal", [Value total]

),

Dates[Year] >= fromwhatyear

)

RETURN

IF (

HASONEVALUE ( Dates[date] ),

IF (

SELECTEDVALUE ( Dates[Year] ) >= fromwhatyear,

CALCULATE (

[Value total],

Dates[Year] >= fromwhatyear

),

IF (

SELECTEDVALUE ( Dates[Year] ) < fromwhatyear,

BLANK ()

)

),

SUMX (

newtable,

[@valuetotal]

)

)

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

https://drive.google.com/file/d/18zhpnNflFiPPQxvrc0ePAGQUe8QeDmNt/view?usp=sharing 

[mdaamirkhan · Accepted solution]

I appiled your dax but it shwoing same value duplicate 

Total past revenue_2018 =

VAR fromwhatyear = 2018

VAR newtable =

CALCULATETABLE (

SUMMARIZE (

ALLSELECTED ( Invoice_Report ),

Invoice_Report[Invoice date],

"@valuetotal", [Total past revenue]

),

Invoice_Report[Year] >= fromwhatyear, ALLEXCEPT(Invoice_Report,Invoice_Report[Contract Code])

)

RETURN

IF (

HASONEVALUE ( Invoice_Report[Invoice date] ),

IF (

SELECTEDVALUE ( Invoice_Report[Year] ) >= fromwhatyear,

CALCULATE (

[Total past revenue],

Invoice_Report[Year] >= fromwhatyear

),

IF (

SELECTEDVALUE ( Invoice_Report[Year] ) < fromwhatyear,

BLANK ()

)

),

SUMX (

newtable,

[@valuetotal]

)

)

[PhilipTreacy · Accepted solution]

Hi @mdaamirkhan 

I don't see anywhere that you have provided sample data in an Excel or PBIX file. Please re-post the link.

What you want still remains a little unclear.

Do you want (for example) cards that show the sales for each year?  The meaning of the red lines in your images isn't clear.  I don't know what they are meant to indicate.

regards

Phil

[FKAli]

If i have a seperate Date Table having year . and two other tables one table is of sales and on is of product I want to calculate sales of a particular year on the basis of a particular product .I will display yeat and product selection in slicer and want to display sales in card .what will the changes in the DAX you posted

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if you need to figure it out like below, but please correct me if I were wrong.
Please check the below DAX measure, picture, and link.

Value total from year2021 =

VAR fromwhatyear = 2021

VAR newtable =

CALCULATETABLE (

SUMMARIZE (

ALLSELECTED ( Data ),

Data[date],

"@valuetotal", [Value total]

),

Dates[Year] >= fromwhatyear

)

RETURN

IF (

HASONEVALUE ( Dates[date] ),

IF (

SELECTEDVALUE ( Dates[Year] ) >= fromwhatyear,

CALCULATE (

[Value total],

Dates[Year] >= fromwhatyear

),

IF (

SELECTEDVALUE ( Dates[Year] ) < fromwhatyear,

BLANK ()

)

),

SUMX (

newtable,

[@valuetotal]

)

)

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

https://drive.google.com/file/d/18zhpnNflFiPPQxvrc0ePAGQUe8QeDmNt/view?usp=sharing 

[mdaamirkhan · Accepted solution]

I appiled your dax but it shwoing same value duplicate 

Total past revenue_2018 =

VAR fromwhatyear = 2018

VAR newtable =

CALCULATETABLE (

SUMMARIZE (

ALLSELECTED ( Invoice_Report ),

Invoice_Report[Invoice date],

"@valuetotal", [Total past revenue]

),

Invoice_Report[Year] >= fromwhatyear, ALLEXCEPT(Invoice_Report,Invoice_Report[Contract Code])

)

RETURN

IF (

HASONEVALUE ( Invoice_Report[Invoice date] ),

IF (

SELECTEDVALUE ( Invoice_Report[Year] ) >= fromwhatyear,

CALCULATE (

[Total past revenue],

Invoice_Report[Year] >= fromwhatyear

),

IF (

SELECTEDVALUE ( Invoice_Report[Year] ) < fromwhatyear,

BLANK ()

)

),

SUMX (

newtable,

[@valuetotal]

)

)

[Jihwan_Kim]

Hi, @mdaamirkhan 

Thank you for your feedback.

I think, if the model is different, sometimes it creates an unexpected result.

Did you see my sample PBIX file?

I think, at least it works in my sample PBIX file.

If you can share your sample pbix file, please share it. 

If it is possible, I can try to create another measure based on your model.

Thank you very much.

[PhilipTreacy · Accepted solution]

Hi @mdaamirkhan 

Download sample PBIX file

Your question isn't very clear and you have only provided a column of dates, no other data.

Are you trying to calculate sales for a selected year e.g. Sales for 2019, Sales for 2020 etc?

If so, and if you have data like this,

then you just need a slicer showing the years in your dates and this measure

Sales for Year = IF(ISFILTERED('Table'[Date].[Year]),CALCULATE(SUM('Table'[Sales]), FILTER(ALL('Table'), YEAR('Table'[Date]) = YEAR(MAX('Table'[Date])))),"")

This isn't using a DateTable.  If you have a dateTable the measure will be different.  Please provide more information and an exampleof your desired result.

regards

Phil

[mdaamirkhan]

without any yrar slicer I want to calculate sales amount starting from 2021 til so on.

As I have few years like 2019 , 2020 , 2021 , 2022 , 2023, 204

[Jihwan_Kim]

Hi,

I was reading your question, but sorry to say that your attached picture made me a little bit confused.

May I ask what is the meaning of the red-color marks?

If it is OK with you, please share the sample data, then I can try to create a DAX, if possible.

Thank you very much.

[mdaamirkhan]

I already share the data in the excel file also share the link to download. red color line mean I am referring that date is like that. 

[mdaamirkhan]

still I didn't get any update 

[mdaamirkhan]

Will I get any solution from the group