Forum Discussion

Jkaelin's avatar
Jkaelin
Icon for Resolver I rankResolver I
8 years ago
Solved

DatesYTD isn't working

Good afternoon,   I have a YTD that isn't working correctly.  If a forecast value is not available, then it should return the actual revenue value.  The picture below shows that works well.  Howeve...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    Hi Jkaelin,

     

    I made one sample for your reference. Creating the measures as below.

     

    Monthly Forecast = SUM(Table1[Forecast])
    Monthly Revenue = SUM(Table1[Revenue])
    Forecast M = IF(ISBLANK([Monthly Revenue]),[Monthly Forecast],[Monthly Revenue])
    Measure =
VAR a =
    TOTALYTD ( [Monthly Revenue], dimDate[Date] )
VAR b =
    TOTALYTD (
        CALCULATE (
            [Monthly Forecast],
            FILTER ( Table1, [Monthly Revenue] = BLANK () )
        ),
        dimDate[Date]
    )
RETURN
    IF ( ISBLANK ( [Monthly Revenue] ), a + b, a )

     

    Also attached the pbix for your reference. If it doesn't meet your requirement, kindly share your sample data and excepted result to me.

     

    Regards,

    Frank

     

     

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi Jkaelin,

 

I made one sample for your reference. Creating the measures as below.

 

Monthly Forecast = SUM(Table1[Forecast])
Monthly Revenue = SUM(Table1[Revenue])
Forecast M = IF(ISBLANK([Monthly Revenue]),[Monthly Forecast],[Monthly Revenue])
Measure =
VAR a =
    TOTALYTD ( [Monthly Revenue], dimDate[Date] )
VAR b =
    TOTALYTD (
        CALCULATE (
            [Monthly Forecast],
            FILTER ( Table1, [Monthly Revenue] = BLANK () )
        ),
        dimDate[Date]
    )
RETURN
    IF ( ISBLANK ( [Monthly Revenue] ), a + b, a )

 

Also attached the pbix for your reference. If it doesn't meet your requirement, kindly share your sample data and excepted result to me.

 

Regards,

Frank

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi v-frfei-msft,

i tried testing the same and i am able to recreate what james was talking about.

i also checked your file "Dates YTD not working.pbix" file and is working good.Not sure if am missing something in here.

Please find the test file which i created in the below link :

 

YTD Test

 

My file see YTD Measure : 
 YTD Measure = CALCULATE([Measure],DATESYTD('Calendar'[Date]))

 

 

 

 

 

Regards

CLR