DAX flag for Last Completed Quarter

[Joshi_AB7071]

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 October to September.
Last completed Quarter would be True only when all the 3 months of this quarter completed.

Example:
When this month is November 23, (July,August,September 2022 ) - are 2022 Q4 last completed/Full Quarter.

So, July 22,Aug 22,Sep 22 should flag as True and OCT,NOV should flag as False Until OCT,Nov,DEC completes.

Any help would be greatly appreciated.

Thanks in advance,
A Joshi

[lbendlin]

Sounds straightforward (assuming your calendar table is in import mode or has a local copy.  You would create a calculated column that implements your logic.

What have you tried and where are you stuck?

[Joshi_AB7071]

Hi Thanks for your time.

The data source for this dashboard is Azure Blob storage . final requirement is to restrict my bar chart to show data  upto completed quarter.

Im new to Power BI and im planning to create flag for this and want to use in main DAX formula to restrict x axis display. Actually im not getting idea how to create flag for below data.

[Anonymous]

Hi @Joshi_AB7071 ,

How can we judge if every month is completed or not? Is there any fact table in your model? Could you please provide more details(sample data with Text format, screenshot and special examples etc.) on your requirement? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Joshi_AB7071]

Hi,

Dashboard has Amount field to decide wherther the Month is completed or not. 

Since im a new to community, Dont have option to attach sample data file/PBIX.

Alternatively i can give snapshot of my fact table table as attached.

Thanks,

A joshi

[lbendlin]

Screenshots are not helpful.  
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[Joshi_AB7071]

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

[Anonymous · Accepted solution]

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

[Joshi_AB7071]

Thanks for your time. its worked .

Thanks,

A Joshi