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

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

  • Joshi_AB7071's avatar
    Joshi_AB7071
    Regular Visitor
    3 years ago

     

     

    Amount

     

     

    		MonthFYFiscsl period IDFiscal quarted  IDFiscal Period Start Date
     

    100

     

    		OCT
    2022

     

    		265

    89

     

     

    9/26/2021 0:00

     

     

    100Nov20222668910/24/2021 0:00
    100 Dec20222678911/21/2021 0:00
     

    100 

     

    		Jan20222689012/26/2021 0:0
     

    100 

     

    		Feb2022269901/23/2022 0:00
     

    100 

     

    		Mar2022270902/20/2022 0:00

    100

     

    		Apr

    2022

     

    		27191
     

    3/27/2022 0:00

    100May2022272914/24/2022 0:00

    100

     

    		Jun2022273915/22/2022 0:00

    100

    		Jul2022274926/26/2022 0:00

    100

    		Aug

    2022

     

    		275927/24/2022 0:00

    100

    		Sep2022276928/21/2022 0:00

    100

    		Oct2023277939/26/2022 0:00

     

    		Nov20232789310/24/2022 0:00

     

    		Dec20232799311/21/2022 0:00

     

    		Jan20232809412/26/2022 0:00

     

    		feb2023281941/23/2023 0:00

     

    		Mar2023282942/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's avatar
      Anonymous
      Not applicable
      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

      • Joshi_AB7071's avatar
        Joshi_AB7071
        Regular Visitor
        3 years ago

        Thanks for your time. its worked .

         

        Thanks,

        A Joshi