Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Getting actual value as YTD value on inserting a filter

Hi,

I needed to calculate YTD value for a group of Units for two different year and I was able to do and plot a graph. The formulae I used was 
YTDXYX = CALCULATE(SUM(Data[XYX days]);FILTER(ALLSELECTED(Data[Month]);'Data'[Month]<=MAX('Data'[Month])))

 

This gave me data based on month and the value I see in my tabular column is like this which is correct:

Month  2017   2018
1             123     110

2             312      253

3             444      375

4             516      500              and so on... Now this value is correct and I have no issue.

But when I try to filter out and put a visual level filter just for Month like Month is 4 in the above tabular column

I get data as

Month   2017    2018
4             72        125           which is the actual value and not the YTD value. May I know what is causing this issue . I need to put the actual month and get the YTD for different Units and so this filter is important.

Its not behaving correctly though.

Please advice.

Regards,
Ankku

Data Modeling

4 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    8 years ago

    Hi Anonymous,

     

    This is due to the function ALLSELECTED(), the visual level filter will affect the measure values. 

     

    To get the value:

     

    Month   2017    2018
    4             516       500

     

    You can replace the ALLSELECTED() function with ALL() function,  so the new measure is

     

    YTDXYX = CALCULATE(SUM(Data[XYX days]);FILTER(ALL(Data[Month]);'Data'[Month]<=MAX('Data'[Month])))

     

    Best Regards,
    Qiuyun Yu

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi v-qiuyu-msft,

      I still see something is wrong in here. if you see my graph here I get the value as YTD in the graph as below. Please see the value for month 4. The value in graph is different and some other value when I put a filter on month 4. Even in graph if I put the filter as Month 4 I get the same value as in tabular column. But without filter there is a different value in the graph as you can see. What am I missing??

       

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        8 years ago

        Hi Anonymous,

         

        Please share pbix file with us if possible. 

         

        Best Regards,
        Qiuyun Yu

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Try this

     

    =CALCULATE(SUM(Data[XYX days]);DATESYTD(Calendar[Date];"31/12"))

     

    Please note the following:

     

    1. I have assumed that the year ending date is 31 December.  If it is 30 June, then change the last input to "30/6"
    2. There should be a Calendar Table with a relationship from the Date column fo the Data Table to the Date column of the Calendar Table
    3. In the Calendar Table, write these calculated column formulas to extract the Year and Month =YEAR(Calendar[Date]) and =FORMAT(Calendar[Date],"mmmm")
    4. In your visual/filter, drag the Year/month column from the Calendar Table

    Hope this helps.