Filter for calculate data between day 1 to current date/today's date for each month

[Anonymous]

Hello,

I'm looking for a way to calculate my data for each month from 1st date of the month to the current date. Like if today is 21 Oct, then all the data for each month will be calculated from day 1 to day 21.

1 Jan to 21 Jan,

1 Feb to 21 Feb,
1 Mar to 21 Mar

and so on.

I already know this is possible by using Slicer, this can manually cut the data value by day on the Date table.

How do I make the filter (like how the Slicer did) inside the Calculate formula to just get the day 1 to the current date/today's date for each month?

[mahoneypat · Accepted solution]

Thank you for the clarification.  Here is one way to do that.

Through Same Day =
VAR todayday =
    DAY ( TODAY () )
VAR mindate =
    MIN ( 'Date'[Date] )
VAR maxdate =
    DATE ( YEAR ( mindate ), MONTH ( mindate ), todayday )
RETURN
    CALCULATE (
        [Total Sales],
        FILTER ( ALL ( 'Date' ), 'Date'[Date] >= mindate && 'Date'[Date] <= maxdate )
    )

Pat

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Your case seems very interesting, it took me a while and then I came up with a solution, and if it helps you that's great.

Here is my measure:

Measure__lastdate = 
var _r=CALCULATE(DAY(MAX([Date])),ALL('Table'))
var _result=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))&&'Table'[Date]<=EOMONTH(MAX('Table'[Date]),-1)+_r))
return _result

If you want the data to dynamically change like the slicer above does, then create a what if parameter.

so the measure:

Measure = 
var _result=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))&&'Table'[Date]<=EOMONTH(MAX('Table'[Date]),-1)+[Days Value]))
return _result

Data:

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , use datesytd with help from date table

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

based on today

MTD=
var _min = eomonth(today(),-1)+1
var _max = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Anonymous]

Hi @amitchandak, thank you for your response.
I have already tried your solution formula Based on today, but the result only shows today month. I want to get the result for each month, not just October (Today month). That's close but not what I want to achieve.

[mahoneypat]

Try Amit's first example with DATESMTD().  If you have a Date table (and marked as a date table), it should work.

Pat

[Anonymous]

Hi @mahoneypat, thank you for your response.

I'm sorry seems I didn't explain completely what I want to achieve here. From what @amitchandak gives, I already have the calculation for MTD.

What am I trying to achieve is, calculate my data for each month based on the date today.
Example :

If today's date is 22 Oct, then the calculation will only show for each month from range 1 to 22.
1 Jan to 22 Jan
1 Feb to 22 Feb

and so on.

So not the Month till Date, this only shows this month to today's date, but the other month shows the Total to the end of the month.
I still can't do this without using Slicer, if there is no way to achieve this without it then maybe I really have to manually use the Slicer to cut the data between the day range I want.

[mahoneypat · Accepted solution]

Thank you for the clarification.  Here is one way to do that.

Through Same Day =
VAR todayday =
    DAY ( TODAY () )
VAR mindate =
    MIN ( 'Date'[Date] )
VAR maxdate =
    DATE ( YEAR ( mindate ), MONTH ( mindate ), todayday )
RETURN
    CALCULATE (
        [Total Sales],
        FILTER ( ALL ( 'Date' ), 'Date'[Date] >= mindate && 'Date'[Date] <= maxdate )
    )

Pat

[Anonymous]

@mahoneypat Thank you so much, your solution is perfect. 

I try to modify a little from your formula. I change the DAY(TODAY()) to DAY(LASTDATE[mydatetable])  because I want to refer to the latest Date in my table for the FILTER, but I got an Error,
"An argument of function 'DATE' has the wrong data type or the result is too large or too small"
I assume that the result from LASTDATE[mydatetable] is too large, but I'm still not sure. I have a Date table that has relation with my date column in my table, I'm still questioning do I have to use the Date Table or the date column from my data? 

[mahoneypat]

You could try MAXX(ALL(Table[Date]), Table[Date]) to get the last date of your table in spite of any filters.

Pat