Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Total Sales for partial year

Hi,

I have a measure "TotalSalesYTD"

TotalSalesYTD = CALCULATE (
Sum(Order[Sales]),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Year] = MAX ( 'Calendar'[Year] )
&& 'Calendar'[Month] = MAX ( 'Calendar'[Month] )))

This is giving the results I wanted for any selected year except for current year 

Obviously, for current year I have the data only till this month

How can I tweak this meausre to also display results for Current year upto current month

 

Note: My data is a monthly snapshot data 

          I have only one filter "Year" 

 

Here's the sample data:

Even though I have year 2020 data till today, the measure is not able to display it

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi,

    Thanks you all for your  prompt responces

     

    I was able to achieve this by using the below DAX

     

    TotalSalesIncludingPartialYear=
    Var MaxMonth = MONTH(LASTDATE(Orders[OrderDate]))
    RETURN
    CALCULATE([TotalSales], 'Calendar'[Month]=MaxMonth)

     

    Thank you all

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,
    According to my understanding, you want to calculate the sum of sales ahead of current year date, right?

    Here is my data sample, then use the following formula:

     

    Filtered table =
FILTER ( 'Order', 'Order'[Date] < NOW () )
    Order year =
YEAR ( 'Filtered table'[Date] )
    TotalPerYear =
CALCULATE (
    SUM ( 'Filtered table'[Sales] ),
    ALLEXCEPT ( 'Filtered table', 'Filtered table'[Order year] )
)

     

    My visualizations look like this:


    Is the result what you want? If not, please upload some data samples and expected output.
    Please do mask sensitive data before uploading.

     

    Best Regards,
    Eyelyn Qin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

    Thanks you all for your  prompt responces

     

    I was able to achieve this by using the below DAX

     

    TotalSalesIncludingPartialYear=
    Var MaxMonth = MONTH(LASTDATE(Orders[OrderDate]))
    RETURN
    CALCULATE([TotalSales], 'Calendar'[Month]=MaxMonth)

     

    Thank you all