topic Re: Comparing the same days of the current month year to year in DAX Commands and Tips

Comparing the same days of the current month year to year

jkoclejda — Wed, 18 Jan 2023 09:51:42 GMT

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.

Re: Comparing the same days of the current month year to year

Greg_Deckler — Tue, 17 Jan 2023 15:55:50 GMT

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

Re: Comparing the same days of the current month year to year

jkoclejda — Tue, 17 Jan 2023 17:16:48 GMT

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

Re: Comparing the same days of the current month year to year

Anonymous — Wed, 18 Jan 2023 07:00:22 GMT

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

Re: Comparing the same days of the current month year to year

jkoclejda — Wed, 18 Jan 2023 09:48:51 GMT

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.

Re: Comparing the same days of the current month year to year

PaulOlding — Wed, 18 Jan 2023 13:55:32 GMT

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