Power BI Dax - How to use a selected date value in a measure

[AH2022]

Hi All,

I want to make a calculation based on a fiscal period. The code DAX for 22-23 (2022/04/01-2023/03/31)  works.

NbreClientsContractNOTRenewed22-23 =

CALCULATE(

COUNT(TableClients[id]), FILTER(TableContracts, TableContracts[ContractsEndingOn(Future)]=0 && TableContracts[ContractsEndingOn(Past)]<=DATE(2023,03,31) &&

TableContracts [ContractsEndingOn (Past)]>=DATE(2022,04,01)))

How can I write the code, and calculate based on a fiscal period selected by the user , within a slicer?

User selects a FiscalYear (From a Slicer)

DimDate[Date] between

NbreClientsContractsNOTRenewedFYSelectedByUser =

CALCULATE(

COUNT(TableClients[id]), FILTER(TableContracts, TableContracts[InstallmentsEndingOn(Future)]=0 &&

TableContracts[InstallmentsEndingOn(Past)]= FYSelectedByUser

//Calculates NbreClientsContracts finishing FY Selected By User

Thank You.

[Jihwan_Kim · Accepted solution]

Hi,

Please try the below if it works.

NbreClientsContractNOTRenewed22-23 =
VAR _minselecteddate =
    MIN ( Calendartable[Date] )
VAR _maxselecteddate =
    MAX ( Calendartable[Date] )
RETURN
    CALCULATE (
        COUNT ( TableClients[id] ),
        FILTER (
            TableContracts,
            TableContracts[ContractsEndingOn(Future)] = 0
                && TableContracts[ContractsEndingOn(Past)] <= _maxselecteddate
                && TableContracts[ContractsEndingOn (Past)] >= _minselecteddate
        )
    )

[AH2022]

Thank you @Jihwan_Kim ,

I need to refine my question.

ContractsEndingOn(Past) is a calculated column.

The calculated column ContractsEndingOn(Past) allows the user to specify the “fiscal period” within a slicer and keeps the implicit Filter of the report on the page. Therefore, it filters my visual and the calculation of the respective number of customers is done according to the ContractsEndingOn(Past) ( “fiscal period” selected by the user.)

The problem arises when I want to make a table summarizing the number of clients who have renewed their contract, sorted by fiscal period.

Because a contract can be signed for example in 22-23 and can end in 26-27.

I need to display the fiscal period, based on the contract end date.

I have this:

I need to calculate the “Fiscal Year” based on the column TableContracts[ContractsEndingOn(Past)] :

In summary, I need to create a fiscal year column (CalculatedColumn)  from another Date calculated column TableContracts[ContractsEndingOn(Past)].

01/April/Year1 TO 31/March/Year2  returns Year1 – Year2

01/April/2022 TO 31/March/2023  returns 22-23 OR 2022-2023 

Thank you !

[Jihwan_Kim]

Hi,

Sorry that it is difficult to understand the problem.

If it is OK with you, please share your sample pbix file's link and the expected outcome of the visualization, and then I can try to look into it to come up with a more accurate solution.

Thanks.

[AH2022]

Hi Kim,

I accepted your answer as a solution for the first part of the question. Thank You.

For the 2nd part, (determine Fiscal Year from a calculated column) this post helped me. Solved: Re: Fiscal Year Calculated Columns - Microsoft Power BI Community  

Good day.