Comparing the same days of the current month year to year

[jkoclejda]

Hi!

I have a simple bar chart that shows monthly revenue broken down by years

It's January now, and there's no point in comparing half a month to whole month in previous years. 

I am wondering how to write a calculation formula that would show the sum for the period of days that have passed in the current month and the total for the remaining months?

So today (January 17, 2023) , for past years, the chart will show a comparison only up to and including January 16. If today was February 3rd, the chart will show full January (and the remaining months after February), but for February only up to the 3rd day.

[PaulOlding · Accepted solution]

Hi @jkoclejda 

Here's a measure that uses a separate date table (called Baseline Date in my model) that has a month number column.  You would replace the 'Baseline Activity Count' measure with your revenue (or whatever) measure.

Monthly Comparison = 
VAR _Today = TODAY()
VAR _Month = SELECTEDVALUE('Baseline Date'[Baseline Month Num])
VAR _CurrentMonth = MONTH(_Today)
VAR _Result = 
IF(_CurrentMonth = _Month,
    CALCULATE(
        [Baseline Activity Count],
        //get dates to today
        FILTER(VALUES('Baseline Date'[Baseline Date]), DAY('Baseline Date'[Baseline Date]) <= DAY(_Today))
    ),
    //use the measure as is, in effect getting all dates for the month
    [Baseline Activity Count]
)
RETURN
    _Result

[Anonymous]

Hi  @jkoclejda ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Flag =
var _todat=TODAY()
return
IF(
    MAX('Table'[Month]) = FORMAT(_todat,"mmm"),1,0)

Measure =
var _today=
TODAY()
return
SUMX(
    FILTER(ALLSELECTED('Table'),    'Table'[Year]=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(_today)&&DAY('Table'[Date])<=DAY(_today)),[Value])

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

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

[jkoclejda]

Thank you for your answer but that's not quite what I'm looking for. I would like the chart to show full data for months other than the current one and comparison to the current day only for the current month. 

On January 18 2023, the chart will show a comparison of January 1-17 for individual years - 2020, 2021, 2022, 2023. The remaining months (February to December) for the years 2020-2022 will be presented normally.

On March 7, 2023, the chart will show a comparison of March 1-6 for the years 2020-2023. Full January and February data for 2020-2023 and full April-December data for 2020-2022.

[Greg_Deckler]

@jkoclejda You can use DAX's EDATE function for things like that.

[jkoclejda]

@Greg_Deckler Can you give me an example based on my case? Should I use it with IF to make it works only with current month