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 Octobe...
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

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

      • Joshi_AB7071's avatar
        Joshi_AB7071
        Regular Visitor
        3 years ago

        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