Date Hierarchy extending beyond last date in dataset

[Anonymous]

I have a data set of pipeline milestone activity from 1/1/18 to today (5/31/19), and I'm trying to show a YTD comparison of 2018 and 2019 pipeline volume by milestone (i.e YTD 5/312018 vs YTD 5/31/19). I'm using the following fields: [CreateDate] (transaction date the time intelligence is based on), [Milestone] and [NewVolume]. When I pull the [CreateDate] into the table, the values start at January 2018 (using only year and month) and continue through December 2019, eventhough the last date in my dataset is 5/31/19. Wondering how I can get it to stop at the end of my dataset and not extend through the end of 2019? The table below shows my data by month as well as the YTD and YTD Last Year columns. In the visual, it shows YTD December, which is not accurate. Thanks in advance for your help!

[Ashish_Mathur]

Hi,

Try this measure

=IF(ISBLANK(your_measure),BLANK(),your_measure)

Hope this helps.

[Anonymous]

I tried this, it didn't work because of the date heirarchy extending to December 2019. The YTD formulas think that YTD is December 2019.

[v-lili6-msft]

hi, @Anonymous 

Just add a conditional in your formula:

Comm Vol YTD = 
IF (
    MIN ( 'History: Loan'[CreatedDate].[Date] )
        <= CALCULATE ( MAX ( 'History: Loan'[CreatedDate] ), ALL ( 'History: Loan' ) ),
    TOTALYTD('History: Loan'[Total New Comm],'History: Loan'[CreatedDate].[Date])
)

Comm Vol YTD Last Year = IF (
    MIN ( 'History: Loan'[CreatedDate].[Date] )
        <= CALCULATE ( MAX ( 'History: Loan'[CreatedDate] ), ALL ( 'History: Loan' ) ),
calculate([Comm Vol YTD],SAMEPERIODLASTYEAR('History: Loan'[CreatedDate].[Date])))

Result:

Best Regards,

Lin

[Anonymous]

@v-lili6-msftThe condition added to the formula worked on the visual that is by month (top visual in picture), however, when I go up a level and total by year (bottom visual in picture), the 'New Com Vol YTD Last Year' still shows YTD Dec and not YTD June. The by year visual is what I want to use in my report. I'm going to remove the solution flag and see if we can figure out the YTD values or what is causing the date heirarchy to extend past the end of the data set.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

@Ashish_Mathur Sorry I cannot share my PBI file as it contains sensitive data.

[calerof]

Hi @Anonymous ,

First make sure you have a date table.

Then you could try using the following YTD measures after your first Total New Comm aggregation:

Sales YTD = IF(
                MIN( 'Calendar'[Date] ) <= CALCULATE( MAX( 'History: Loan'[Date] ), ALL( 'History: Loan' ) ),
                        CALCULATE(
                                'History: Loan'[Total New Comm],
                                DATESYTD( 'Calendar'[Date] )
                        )
                    )

Sales YTD LY = VAR DataMaxDate = 
                                CALCULATE( MAX( 'History: Loan'[Date] ), ALL( 'History: Loan' ) )
                      RETURN
                                CALCULATE(
                                        'History: Loan'[Sales YTD],
                                        SAMEPERIODLASTYEAR(
                                            INTERSECT(
                                                VALUES( 'Calendar'[Date] ),
                                                DATESBETWEEN( 'Calendar'[Date], BLANK(), 
                                                DataMaxDate )
                                            )
                                        )
                                )

Hope it helps.

Cheers,

Fernando

[calerof]

Hi @Anonymous ,

In order to be able to help you, it'd be nice if you share your formulas.

Best regards,

Fernando

[Anonymous]

I believe the issue is with the date hierarchy, however here are my formulas:

Total New Comm = sum('History: Loan'[New Comm. Vol.])

New Comm Vol YTD =

TOTALYTD('History: Loan'[Total New Comm],'History: Loan'[CreatedDate].[Date]))

New Comm Vol YTD Last Year = calculate([New Comm. Vol. YTD],SAMEPERIODLASTYEAR('History: Loan'[CreatedDate].[Date]))