How to display sum value for first date in July of each year?

[alya1]

Hello,

I have a data table like below:

Date	ID	Number
7/1/20	111	1
7/1/20	222	2
7/2/20	111	1
7/2/20	222	2
... everyday till
7/1/21	111	1
7/2/21	111	1
... everyday till
7/2/22	222	2
7/2/22	333	2
... everyday till today

I want to display a table visual like below only showing the sum of Number for the first date in each year's July like below: 

Date	Sum of Number
7/1/20	3
7/1/21	1
7/2/22	4

So far my table visual is just displaying every single date. Is there a measure to limit just the first date in every July?
Thank you very much!

[Ashish_Mathur]

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas of Year, Month name and Month number.  Sort the Month name column by the Month number column.
2. Create a relationship (Many to One and Single) from the Date column of the Data table to the Date column of the Calendar Table
3. To your visual, drag Year and Month name from the Calendar Table
4. Write these measures

Total = sum(Data[Number])

Total on first day = calculate([Total],datesbetween(calendar[date],min(calendar[date]),min(calendar[date])))

Hope this helps.

[Irwan]

hello @alya1 

please check if this accomodate your need.

1. create a calculated column with following DAX for finding the minimum date of each July.

Min = 
var _Year = 'Table'[Date   ]
var _Date = EOMONTH('Table'[Date   ],-1)
var _Month = MONTH('Table'[Date   ])
Return
MINX(
    FILTER(
        'Table',
        'Table'[Date   ]>_Date&&
        _Month=7
    ),
    'Table'[Date   ]
)

2. create new table with following DAX for displaying the result

Summarize = 
SUMMARIZE(
    FILTER(
        'Table',
        not ISBLANK('Table'[Min])
    ),
    'Table'[Min],
    "Sum of Number",
    CALCULATE(SUM('Table'[Number]),'Table'[Date   ]='Table'[Min])
)

Hope this will help.
Thank you.

[alya1]

Thank you Irwan! I think I'm really close. But for the calculated column, I keep getting value 6/30/YYYY filled for all July dates. My raw data does have mostly everyday of the year. Do you know how to fix this so it returns the first date in July instead of 6/30th please? 

[Irwan]

Hello @alya1 

I am not sure, but it should return 30-June because you are looking the first date in July.

Thats why in calculated column, the DAX will collect value greater than _date which is return as 30-June.

As you can see in example above, it will return as first date of July, and other month will be blank since you only want July.

Not sure, but please check again your DAX.

Thank you.