Last sale Date Period

[ECOBHV]

Dear PBI Support Team,

I would like to calculate only the Total sales base on Last Sales Date Data as below, please advice me with DAX. I want to calculate Total Sale From Jan - June , current Date is August. This video has the direction but not all https://www.youtube.com/watch?v=WBddNp_25YY 

Thanks you all,

ECOBHV

[v-piga-msft]

Hi @ECOBHV ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[ECOBHV]

Dear V-piga-msft,

Thank you for your support. In your DAX, I would like to replace number "6" by the max month of current year in my sales data ( June 2019). In the future, when I add more data (July 2019, August 2019), your DAX will automatically re-calculated.

Thanks and best regards,

[v-piga-msft]

Hi @ECOBHV ,

For your requirement, you could replace the "6" to Month(Today()) and have a try.

If you still need help, please share some data sample and your desired output.

Best Regards

Cherry

[ECOBHV]

Hi v-piga-msft ,

Month(today()) will return "11" while I want to perform Month(LastDate(Table'[Column]) that now still "6" because the last date in my database is 30 Jun 2019.

Please see my attachment

Thank you so much for your supports

[ECOBHV]

Dear v-piga-msft,

If I replace the "6" to Month(Today()), Year 2018 will show July, August and September. I want to show the max month of the max year in my sales data (but not max month of current date) that is now Jun and maybe July when I add more data.

Thanks and best regards,

[v-piga-msft]

Hi @ECOBHV ,

I'm a little confused about your requirement.

Do you want to get the total Sale From Jan - June for each year or the current year?

In addition, do you want to calculate the total sale until the current month?

For example, if you have the sales in June and August for year 2019, do you want to calculate the total saled from Jan-Aug or just Jan - June?

I have made an example if you want to calculate the sales from Jan - June for each year, please refer to the formula below.

sale_jan_jue =
CALCULATE (
    SUM ( 'table'[Revenue] ),
    FILTER (
        'table',
        YEAR ( 'table'[Date] ) = YEAR ( MAX ( 'table'[Date] ) )
            && MONTH ( 'table'[Date] ) <= 6
    )
)

Here is the output.

Hope this can help you.

If you still need help, please share your desired output so that we could understand your requirement better.

Best Regards,

Cherry