Minimum date at which a measure is not blank

Sample 2.pbix (190.3 KB)

I need to find the Number of months left till funding is required, from the current month.

For eg:
In November 2020- Funding = 15.
December - Funding =0
January- Funding =15

So in this case my desired measure value = 1, ie. 1 month left till funding is required in January

Hi @Anu,

Thanks for providing a PBIX :+1:

First created this measure:

NextFunding = 
CALCULATE( MIN( 'Date'[MonthnYear] ),
    FILTER(
        FILTER( ALL( 'Date' ), 'Date'[MonthnYear] > MAX( 'Date'[MonthnYear] )
    ),  NOT( ISBLANK( [Funding] ))
))

And then this one:

Month's to next Funding = 
VAR nFund = [NextFunding]
VAR cMonth = MAX( 'Date'[MonthnYear] )
RETURN

IF( NOT( ISBLANK( [Closing Balance (Correct Totals)] )),
    CALCULATE( DISTINCTCOUNTNOBLANK( 'Date'[MonthnYear] ),
        FILTER(
            FILTER( ALL( 'Date' ), 
                'Date'[MonthnYear] > cMonth &&
                'Date'[MonthnYear] <= nFund
        ),  ISBLANK( [Funding] )
    ))
)

.

The IF statement keeps month’s without values hidden.

Here’s your sample file: eDNA - Month’s till Next Funding.pbix (188.3 KB)
I hope this is helpful.

1 Like

Dear Melissa,

Thanks for the detailed reply!

But I want this as a card visual, which shows me only the value = 1,ie. from the current month, how many months are left until next funding is required.(Only Current month needs to be considered).
So my Card visual will show 1 ,as per this example, since current month is November.

Hi @Anu,

:thinking: You hadn’t mentioned that requirement in your original post…

Please know that this forum is full of volunteers that give up their own time to help others, all we ask in return is that if you post a question you also provide as much detail and mock up of what you are trying to achieve so we don’t needlessly have to re iterate a provided solution.

The quick fix - is to place a relative Date slicer on the card visual providing that context as depicted below.

image

I hope this is helpful

3 Likes

Sorry for not mentioning Card visual in particular, will try to include maximum details next time.
Also, as mentioned the desired value is value = 1,ie. from the current month, how many months are left until next funding is required.(Only Current month needs to be considered).
So my Card visual will show 1 ,as per this example, since current month is November.

1 Like

could you please attach the pbix as well

I’m away but will do so in a couple of hours when I get back

1 Like

Hi @Anu,

Updated the relative date filtering to give you the expected result.

image

Here’s your sample file. eDNA - Month’s till Next Funding.pbix (189.0 KB)
I hope this is helpful.

1 Like