Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

TotalYTD function as a graph for different years

Hello guys,   I want to build a measure to show the revenues per month for each year. I have done that, but they should be summarized each month.   I have two examples for that, how it should loo...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous

    After my research, you could try this way:

    Step1:

    Add a year and month column for date column

    Step2:

    Use this formula to create a TotalYTD Measure

    Measure = CALCULATE(SUM(Table1[Qty]),FILTER(ALL(Table1[Month]),Table1[Month]<=MAX(Table1[Month])))

    Step3:

    Drag month field into X-Axis and Year field into Legend

    Result:

     

    here is pbix file, please try it.

     

    Best Regards,

    Lin

     

     

     

jvangrunderbeec's avatar
jvangrunderbeec
Helper I
7 years ago

Try setting your months on the axis and your year in the legend.

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi jvangrunderbeec,

 

thanks for your fast reply, but when I will do that, I have the problem that I only see the year 2019 with the TotalYTD function. When I choose only the revenue as the graph instead of the TotalYTD function, I will have the same like in the first screenshot.

  • jvangrunderbeec's avatar
    jvangrunderbeec
    Helper I
    7 years ago

    Anonymous

     

     

    Are you sure then that the number is correct? isn't is counting all your date? So it says 2019 but it is for all the yaers

  • jvangrunderbeec's avatar
    jvangrunderbeec
    Helper I
    7 years ago

    Try this formula:

     

    Cumulative Revenue =
    VAR CurrDate =
    MAX ( DateTable[Date] ) //Insert datetable date column
    Var CalculateddMeasure =
    CALCULATE(sum(Revenue)) //Insert to calculate number
    RETURN
    //Total
    IF (
    ISBLANK ( CalculateddMeasure );
    BLANK ();
    IF (
    COUNTROWS ( FactTable ) > 0; //Insert FactTable name
    CALCULATE (
    CalculateddMeasure;
    FILTER (
    ALLSELECTED ( DateTable ); //Insert Date Table name
    DateTable[Date] <= CurrDate //Insert Date Table Date column
    && YEAR ( DateTable[Date] ) = YEAR ( CurrDate ) //Insert Date table Date column
    )
    );
    BLANK ()
    )
    )
    )