Forum Discussion

vincentpit's avatar
vincentpit
Frequent Visitor
8 years ago
Solved

Get the YTD formula to work

Hello,   I'm a beginner at Power BI and i'm trying to get the YTD measure to work, but i can't seem to get it right..   What i did is: I have an invoice tables INVOICES containing invoices sinc...
Need Help
  • vik0810's avatar
    vik0810
    8 years ago

    I downloaded your file and used this measure, it works.

     

    ColumnYTD =
VAR Day1 =
    DAY ( NOW () )
VAR Month1 =
    MONTH ( NOW () )
VAR Year1 =
    MAX ( DATUM[Year] )
RETURN
    CALCULATE (
        SUM ( FACTUREN[TOTAAL] ),
        AND (
            DATUM[Date] >= DATE ( Year1, 1, 1 ),
            DATUM[Date] <= DATE ( Year1, Month1, Day1 )
        )
    )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

Take a look at TOTALYTD:

 

https://msdn.microsoft.com/en-us/library/ee634400.aspx

 

This is what the quick measure brings back:

 

Column YTD = 
IF(
	ISFILTERED('Calendar'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	TOTALYTD(SUM('Calendar'[Column]), 'Calendar'[Date].[Date])
)
    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      8 years ago

      OK, downloaded file. I created the TOTALYTD forumla using this measure:

       

      TOTAAL YTD = 
IF(
	ISFILTERED('DATUM'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	TOTALYTD(SUM('FACTUREN'[TOTAAL]), 'DATUM'[Date].[Date])
)

      Is $12,796,464.03 not the right answer for 2017?

       

      It seems like the right answer as I created a slicer and set it to 1/1/2017 - 10/30/2017 and it returned the same answer. Is the issue that the previous years are not returning the total for the same time period but rather the entire year?

      • vincentpit's avatar
        vincentpit
        Frequent Visitor
        8 years ago

        The right answer for 2017 is correct $12,796,464.03

         

        I want to compare this revenue to the other years same period revenue.

        revenue 2015 1-1 till 30-10

        revenue 2016 1-1 till 30-10

         

        If i filter on date the returned values should be:

        2015: $11,571,369.09

        2016 :$12,381,482.41

         

        at this moment the returned values are revenues for the whole 2015 and 2016 year.

         

        Edit: When i use the formula you handed me i get no values in 2015 & 2016.