Year and month picker with current and last year values

[Anonymous]

I have a table with a date column, sales column (and some other category columns) and I would like to create two slicers that would allow me to pick a year and a month. Based on the selection, I would like to have two separate measures that would calculate cumulative sum of sales current year, and cumulative sum of sales last year, and also two measures that would do count instead of sum. Besides that, on the visual I have a few other slicers that I would like to affect my measures. How can I do this?

[Anonymous]

Thank you for your answer, I found out this works for me:

Count CY =

VAR _y = SELECTEDVALUE('Table'[Date].[Year])

VAR _m = SELECTEDVALUE('Table'[Date].[MonthNo])

RETURN

CALCULATE(

    COUNT('Table'[ID]),

    FILTER(

        'Table',

        MONTH('Table'[TDate]) <= _m &&

        YEAR('Table'[TDate]) = _y &&

        MONTH('Table'[Date]) = _m

    )

)

However, when I try this code for the Count LY

Count LY =

VAR _y = SELECTEDVALUE('Table'[Date].[Year])

VAR _m = SELECTEDVALUE('Table'[Date].[MonthNo])

RETURN

CALCULATE(

    COUNT('Table'[ID]),

    FILTER(

        'Table',

        MONTH('Table'[TDate]) <= _m &&

        YEAR('Table'[TDate]) = _y-1 &&

        MONTH('Table'[Date]) = _m &&

        YEAR('Table'[Date]) = _y-1

    )

)

This does not work adequately. It should somehow not filter the table based on the selected year and month, but I am not sure how to write that.

[rubayatyasmin]

Try this one. 

Count LY =
VAR _y = SELECTEDVALUE('Table'[Date].[Year])
VAR _m = SELECTEDVALUE('Table'[Date].[MonthNo])
RETURN
CALCULATE(
COUNT('Table'[ID]),
FILTER(
'Table',
YEAR('Table'[TDate]) = _y-1
)
)

By removing the conditions MONTH('Table'[Date]) = _m && YEAR('Table'[Date]) = _y-1, you are no longer filtering based on the selected year and month in the 'Table'[Date] column. This change allows the calculation to count the 'ID' values that have a 'TDate' year equal to _y-1 (last year) without considering the selected year and month.

Please note that this modification assumes that you only want to exclude the filter on the 'Table'[Date] column and keep the filter on the 'Table'[TDate] column as it is in your original code. Make sure this matches your intended logic.

or follow my previous solution. That one should work. 

[rubayatyasmin]

Hi, @Anonymous 

Let's go step by step. First, ensure that your date column is in date format. If it's not, you can change it in the Power Query Editor.

Then, you need to create two slicers for the year and the month. For that, you will have to create two new calculated columns, one for the year and another for the month. Here's how you can do it:

1. Click on 'Modeling' tab and select 'New column'. For year, type the following formula:

Year = YEAR(Table[Date])

1. Repeat the process for the month:

Month = MONTH(Table[Date])

After that, you can create slicers based on these new columns.

Now let's move onto creating the measures. Here's how you can create the measure for the cumulative sum of sales for the current year:

Cumulative Sales Current Year = CALCULATE ( SUM ( Table[Sales] ), FILTER ( ALLSELECTED ( Table ), Table[Year] = SELECTEDVALUE ( Table[Year] ) && Table[Date] <= MAX ( Table[Date] ) ) )

For the cumulative sum of sales for the last year, you can use the following measure:

Cumulative Sales Last Year = CALCULATE ( SUM ( Table[Sales] ), FILTER ( ALLSELECTED ( Table ), Table[Year] = SELECTEDVALUE ( Table[Year] ) - 1 && Table[Month] <= MAX ( Table[Month] ) ) )

The measures for the counts instead of the sum would look like this:

Count Current Year = CALCULATE ( COUNT ( Table[Sales] ), FILTER ( ALLSELECTED ( Table ), Table[Year] = SELECTEDVALUE ( Table[Year] ) && Table[Date] <= MAX ( Table[Date] ) ) )

Count Last Year = CALCULATE ( COUNT ( Table[Sales] ), FILTER ( ALLSELECTED ( Table ), Table[Year] = SELECTEDVALUE ( Table[Year] ) - 1 && Table[Month] <= MAX ( Table[Month] ) ) )

These measures will be recalculated based on the selections in your slicers, including other slicers that you have on your visuals.