Comparison LY with conditions

[gkarlo]

 I hope someone can help me, I need to count all projects released comparing from the last year, for example if I select April 2024, so the graphic or table must show me all months back since April 2023.

Table

DATE	PROJECTS
01.23	10
02.23	5
03.23	10
04.23	20
05.23	10
06.23	10
07.23	20
08.23	15
09.23	15
10.23	5
11.23	10
12.23	10
01.24	10
02.24	12
03.24	10
04.24	10

I used SAMPLEDATE but only compares the specific month current year and the last year, but no all months among, and if I select the year, months as May....Dec are counted of the last year even if this year 2024, the months have not passed.

I look forward to your soon comments.

[v-zhouwen-msft · Accepted solution]

Hi @TomMartens ,thanks for the quick reply, I'll add further.

Hi @gkarlo ,

I have assumed some data as follows:

Please follow these steps:

1.Use the following DAX expression to create a column in 'Table'

Month = FORMAT([Date],"MM.YY")

2. Use the following DAX expression to create a table named 'Table 2'

Table 2 =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2024, 12, 31 ) ),
    "Month", FORMAT ( [Date], "MM.YY" ),
    "EndofMonth", EOMONTH ( [Date], 0 )
)

3.Use the following DAX expression to create a measure

Measure = 
VAR _a = SELECTEDVALUE('Table 2'[Month])
VAR _b = MAXX(FILTER('Table 2','Table 2'[Month] = _a),[EndofMonth])
VAR _c = DATE(YEAR(_b)-1,MONTH(_b),1)
VAR _d = COUNTX(FILTER('Table','Table'[Date] >= _c &&'Table'[Date] <= _b),[Projects])
RETURN IF(ISBLANK(_a),COUNT('Table'[Projects]),_d)

4.Final output

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

[TomMartens]

Hey @gkarlo ,

please take the time to create a pbix file that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix file to OneDrive, Google Drive, or dropbox and share the link. If you are using a spreadsheet to create the sample data, share the spreadsheet as well.

Do not forget to explain the expected result based on the sample data you provide.

Regards,

Tom