Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
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
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |