topic Re: Power BI Dax - How to use a selected date value in a measure in DAX Commands and Tips

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

AH2022 — Sat, 06 Aug 2022 06:02:32 GMT

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.

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

Jihwan_Kim — Sat, 06 Aug 2022 06:47:54 GMT

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 ) )

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

AH2022 — Sat, 06 Aug 2022 15:49:06 GMT

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 !

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

Jihwan_Kim — Sun, 07 Aug 2022 05:34:57 GMT

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.

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

AH2022 — Sun, 07 Aug 2022 16:32:28 GMT

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.