Forum Discussion

Krishna09's avatar
Krishna09
Icon for Helper III rankHelper III
2 years ago
Solved

Filter fiscal year data based on two date columns

Hello all,   In Table 1, there are two date fields(one is start and expiration date) and one ID field.  I have a other table with calender date, month, year, fiscal year columns.  I need to creat...
General Comment
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Krishna09 ,

    You can simply add it to the visual's filter and set the condition equal to 1.

    Best Regards,
    Gao
    Community Support Team

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Krishna09 ,

You will need an unconnected fiscal table with a start date and end date for a fiscal year:

Then create a measure like:

Measure = 
VAR _start_date = MAX('FYCalendar'[StartDate])
VAR _end_date = MAX('FYCalendar'[EndDate])
VAR _result = IF(ISBLANK(SELECTEDVALUE('FYCalendar'[FiscalYear]))||(_start_date>=MAX('Table'[Start Date]) && _end_date<=MAX('Table'[Expiration Date])),1)
RETURN
_result

And the output:

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • Krishna09's avatar
    Krishna09
    Icon for Helper III rankHelper III
    2 years ago

    Anonymous , amitchandak ,

    Thank you both.

    is it possible to show only the row based on ID, with out measure count in the table?

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Krishna09 ,

      You can simply add it to the visual's filter and set the condition equal to 1.

      Best Regards,
      Gao
      Community Support Team

      • Krishna09's avatar
        Krishna09
        Icon for Helper III rankHelper III
        2 years ago

        Hello Anonymous , 

        I used the formulae you mentioned, but it works only when I use in table filter.

        --- it is not working if I mention it, then the table is filtered only based on start date. 

        --- AND it is also not working in pie or other visuals.

         

        I have multiple tables connected to this visuals.

         

        Below is the experssion I am using.

        ContractExp_year = CALCULATE(COUNTX(filter('ContractExpiration','ContractExpiration'[[PCW]]Contract (Effective Date)]<= MAX(FiscalYearfFromOctober[Date])&&
        'ContractExpiration'[[PCW]]Contract (Expiration Date)]> MAX(FiscalYearfFromOctober[Date])),'ContractExpiration'[[PCW]]Contract (Contract Id)]),
        CROSSFILTER(FiscalYearfFromOctober[Date],'ContractExpiration'[[PCW]]Contract (Effective Date)],None))