Forum Discussion
Solved
DAX flag for Last Completed Quarter
Hello DAX guru's I'm trying to create a filter in the DimDate table , which would put the flag as True when the data is in last completed Quarter. In my dashboard Fiscal Year starts from Octobe...
Hi Joshi_AB7071 ,
I created a sample file(see the attachment) base on your provided sample data, please check that is what you want. You can create two calculated columns as below to get it:
Fiscal Year Quarter = 'Table'[FY] & IF ( 'Table'[Month] IN { "Oct", "Nov", "Dec" }, "Q1", IF ( 'Table'[Month] IN { "Jan", "Feb", "Mar" }, "Q2", IF ( 'Table'[Month] IN { "Apr", "May", "Jun" }, "Q3", "Q4" ) ) )Flag = VAR _count = CALCULATE ( COUNT ( 'Table'[Fiscal Year Quarter] ), FILTER ( 'Table', 'Table'[Fiscal Year Quarter] = EARLIER ( 'Table'[Fiscal Year Quarter] ) && 'Table'[Amount] > 0 ) ) RETURN IF ( _count = 3, "True", "False" )Best Regards
Amount
| Month | FY | Fiscsl period ID | Fiscal quarted ID | Fiscal Period Start Date |
100
| OCT | 2022
| 265 | 89
| 9/26/2021 0:00
|
| 100 | Nov | 2022 | 266 | 89 | 10/24/2021 0:00 |
| 100 | Dec | 2022 | 267 | 89 | 11/21/2021 0:00 |
100
| Jan | 2022 | 268 | 90 | 12/26/2021 0:0 |
100
| Feb | 2022 | 269 | 90 | 1/23/2022 0:00 |
100
| Mar | 2022 | 270 | 90 | 2/20/2022 0:00 |
100
| Apr | 2022
| 271 | 91 | 3/27/2022 0:00 |
| 100 | May | 2022 | 272 | 91 | 4/24/2022 0:00 |
100
| Jun | 2022 | 273 | 91 | 5/22/2022 0:00 |
100 | Jul | 2022 | 274 | 92 | 6/26/2022 0:00 |
100 | Aug | 2022
| 275 | 92 | 7/24/2022 0:00 |
100 | Sep | 2022 | 276 | 92 | 8/21/2022 0:00 |
100 | Oct | 2023 | 277 | 93 | 9/26/2022 0:00 |
| Nov | 2023 | 278 | 93 | 10/24/2022 0:00 |
| Dec | 2023 | 279 | 93 | 11/21/2022 0:00 |
| Jan | 2023 | 280 | 94 | 12/26/2022 0:00 |
| feb | 2023 | 281 | 94 | 1/23/2023 0:00 |
| Mar | 2023 | 282 | 94 | 2/20/2023 0:00 |
Here is the sample Data for reference, i need to create flag for completed quarter . if we have Amounts for all the 3 Months then the flag should be True otherwise false
Thanks in advance,
A joshi
Hi Joshi_AB7071 ,
I created a sample file(see the attachment) base on your provided sample data, please check that is what you want. You can create two calculated columns as below to get it:
Fiscal Year Quarter =
'Table'[FY]
& IF (
'Table'[Month] IN { "Oct", "Nov", "Dec" },
"Q1",
IF (
'Table'[Month] IN { "Jan", "Feb", "Mar" },
"Q2",
IF ( 'Table'[Month] IN { "Apr", "May", "Jun" }, "Q3", "Q4" )
)
)Flag =
VAR _count =
CALCULATE (
COUNT ( 'Table'[Fiscal Year Quarter] ),
FILTER (
'Table',
'Table'[Fiscal Year Quarter] = EARLIER ( 'Table'[Fiscal Year Quarter] )
&& 'Table'[Amount] > 0
)
)
RETURN
IF ( _count = 3, "True", "False" )
Best Regards
Thanks for your time. its worked .
Thanks,
A Joshi