topic Re: Monthly values by year, up to a specific date in DAX Commands and Tips

Monthly values by year, up to a specific date

Ta_Gaven — Wed, 10 Aug 2022 06:29:06 GMT

Hi,

I have a requirement to create a clustered column chart that can be filtered in a specific way by a date slicer.

The chart needs to display sales by month (x-axis) and year legend. The user has a date slicer and need to be able to set the last date to display on the chart, but it needs to limit previous years also.

This means if the user selects the date june 1th of 2022 in the slicer and we have 3 years of sales data, the chart would display

2022 : sales by month from 1/1/2022 to 6/1/2022
2021 : sales by month from 1/1/2021 to 6/1/2021
2020 : sales by month from 1/1/2020 to 6/1/2020

The data model is a simple fact table with sales linked to a calendar dimension.

Would it be possible to create one dax measure for this calculation ?

Thanks in advance

Re: Monthly values by year, up to a specific date

ValtteriN — Wed, 10 Aug 2022 08:33:13 GMT

Hi,

For LY calculation using SAMEPERIODLASTYEAR combined with calendar should do what you want. So e.g.

CALCULATE ( [measure], SAMEPERIODLASTYEAR ( 'Calendar'[Date]))

Alternatively you could construct the dates for DATESBETWEEN e.g. (use the _ly and _ly2 to create last year and two years ago measures:

var _cy = MAX('Calendar'[Year])

var _ly = _cy-1

var _ly2 = _cy-2

var _cm = MAX('Calendar'[Month])

var _cdate = MAX('Calendar'[Day])

var _edate1 = DATE(_cy,_cm,_cdate)

var _sdate = STARTOFYEAR('Calendar'[Date])

return

CALCULATE([Measure 8],DATESBETWEEN('Calendar'[Date],_sdate,_edate1))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Monthly values by year, up to a specific date

Ta_Gaven — Wed, 10 Aug 2022 08:56:48 GMT

Hi ValtteriN ,

Thanks for your answer.

I thought about having more than 1 measure. However, the user has to be able to see all the years available from the dataset, so that means we would have to create an undefinite number of measure (y, y-1, y-2, ...).

Do you think there is a way to do this in a single measure with dax ?

Re: Monthly values by year, up to a specific date

ValtteriN — Wed, 10 Aug 2022 12:12:02 GMT

Hi,

I see what you are trying to achieve. I should be possible. Here is an example:

Data:

Relationship:

In this case you might not even need dax or jut use simple SUM(table[column])

If you only want last 3 years you can apply a visual level filter where you define this:

Re: Monthly values by year, up to a specific date

Ta_Gaven — Wed, 10 Aug 2022 12:31:05 GMT

Hi,

But in your case it takes the whole month, right ?

In my requirement I have to filter up to the date selected, meaning if the date 6/15/2022 is selected, the sum should from 1/1/YYYY to 6/15/YYYY for each year.