Calculation of sum of sales where monthname in the range of YTD

[Useranu]

Hello,

Please help with below query. How it can be calculated

I have a calendar table and it has Month name(mmm-yy) column.

How to calculate

1. sum(sales) where Month name in the range of YTD

And

2. sum(salesflag)>0 where MonthName = in YTD rang Yes,No

Thanks!

[Useranu]

Thank you

[v-dineshya · Accepted solution]

Hi @Useranu ,

Thank you for reaching out to the Microsoft Community Forum.

Option 1:
SUM(Sales) where MonthName is in the range of YTD:
Use a Date column (not just MonthName)
Even though you have MonthName, the best way to calculate YTD in DAX is using the Date column from your Calendar table.

Sales_YTD =
CALCULATE(
SUM(Sales[SalesAmount]),
DATESYTD('Calendar'[Date])
)
DATESYTD('Calendar'[Date]) dynamically returns all dates from Jan 1st to today (or end of current month if you use a slicer).

Note: This works even if you're visualizing by MonthName like mmm-yy. If you must use the MonthName, you’d need to: Convert it into a sortable format Date, Create a flag or custom logic but it's way messier.

Option 2:
SUM(SalesFlag > 0) Show as Yes/No if value exists in YTD months. If you're trying to flag whether there was any sale in YTD, something like: If any SalesFlag > 0 in YTD → Show Yes Else → Show No

SalesFlag_YTD_Exist =
VAR HasSales =
CALCULATE(
COUNTROWS(Sales),
DATESYTD('Calendar'[Date]),
Sales[SalesFlag] > 0
)
RETURN
IF(HasSales > 0, "Yes", "No")
This will return "Yes" if any record in YTD range has SalesFlag > 0.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

[Useranu]

Need to calculate sum of sales where 

In the range of YTD.

Do I need to use Monthname(is in mmm-yy format) to calculate in YTD.

I have calculated using Date.

Can you please suggest.

Thanks.

[adudani]

Thanks for sharing your valuable insights @lbendlin 

@Useranu , please refer to Re: How to Get Your Question Answered Quickly - Microsoft Fabric Community

Kindly provide a sample input (masking senstitive data) and a sample output in a usable format detailing the issues you are currently facing.

[Useranu]

Need to calculate sum of sales where 

In the range of YTD.

Do I need to use Monthname(is in mmm-yy format) to calculate in YTD.

I have calculated using Date.

Thanks.

[lbendlin]

Date is fine as long as it comes from a well defined calendar table.  Time intelligence functions only really work with proper calendar tables.  Otherwise you would have to recreate the logic manually.  (Many of the Time Intelligence functions are syntax sugar for their underlying code)

[Useranu]

Thank you for the reply.

Monthname in the range of YTD

Can you please let me know how it works.