Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm trying to create a column that will identify a date that is in the month prior to the current month.
I have a calculated column that returns a "Yes" if the date is in the current month as follows:
Closed This Month = IF ( YEAR ( 'Ops Data'[Closed Date] ) = YEAR ( TODAY () ) && MONTH ('Ops Data'[Closed Date] ) = MONTH ( TODAY () ), "Yes", "" )
What I want is a formula that will count the number of dates the month before and also take account of the change of year (so in January 2018 it will count the number of December 2017 dates, etc.)
Hopefully this is a relatively simple request. Any ideas?
Solved! Go to Solution.
Hi @blindeye,
Please try this measure:
Count Dates = IF ( MONTH ( TODAY () ) = 1, CALCULATE ( COUNT ( 'Ops Data'[Closed Date] ), FILTER ( 'Ops Data', 'Ops Data'[Closed Date].[Year] = YEAR ( TODAY () ) - 1 && 'Ops Data'[Closed Date].[MonthNo] = 12 ) ), CALCULATE ( COUNT ( 'Ops Data'[Closed Date] ), FILTER ( 'Ops Data', 'Ops Data'[Closed Date].[Year] = YEAR ( TODAY () ) && 'Ops Data'[Closed Date].[MonthNo] = MONTH ( TODAY () ) - 1 ) ) )
Best regards,
Yuliana Gu
Hi,
Try this for identifying whether the date is prior to the current month or not.
=IF('Ops Data'[Closed Date]<=EOMONTH('Ops Data'[Closed Date],-1),"Yes","No")
Hope this helps.
Thank you both for your responses! Haven't had a chance to try out yet, but will do so ASAP.
Thanks Yuliana, your measure did exactly what I needed
Hi @blindeye,
Please try this measure:
Count Dates = IF ( MONTH ( TODAY () ) = 1, CALCULATE ( COUNT ( 'Ops Data'[Closed Date] ), FILTER ( 'Ops Data', 'Ops Data'[Closed Date].[Year] = YEAR ( TODAY () ) - 1 && 'Ops Data'[Closed Date].[MonthNo] = 12 ) ), CALCULATE ( COUNT ( 'Ops Data'[Closed Date] ), FILTER ( 'Ops Data', 'Ops Data'[Closed Date].[Year] = YEAR ( TODAY () ) && 'Ops Data'[Closed Date].[MonthNo] = MONTH ( TODAY () ) - 1 ) ) )
Best regards,
Yuliana Gu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |