Forum Discussion

Joshi_AB7071's avatar
Joshi_AB7071
Regular Visitor
3 years ago
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 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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    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's avatar
      Joshi_AB7071
      Regular Visitor
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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