Forum Discussion

sven7's avatar
sven7
Frequent Visitor
8 years ago
Solved

In and out-flow from money. Create difference and aggregate value by month.

Dear Power Bi Users,   I am struggling with a little issue right now. I did some research in this forum but did not find the right solution. Maybe I am just on the wrong path, this is why I hope th...
  • MFelix's avatar
    MFelix
    8 years ago

    Hi sven7,

     

    You are using a column you should use a measure, columns are always calculated at row level, measures are calculated at context level so taking into account the different levels of information.

     

    Change your column to a measure and it will work,

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 years ago

If you are using the first measure YTD create a relationship between the two tables.

sven7's avatar
sven7
Frequent Visitor
8 years ago

MFelix This also works now with the connection to the other table and using the balance formula. However, it gives me the same picture again that it does not show the rolling value. I could only link the date between the tables not the month, following error:

 

Thanks Sven

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    Hi sven7,

     

    You are using a column you should use a measure, columns are always calculated at row level, measures are calculated at context level so taking into account the different levels of information.

     

    Change your column to a measure and it will work,

     

    Regards,

    MFelix

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago
    You must link the date columns but use the.month colum from calendar table on your.visual
  • sven7's avatar
    sven7
    Frequent Visitor
    8 years ago

    Yes, I tried that but that does not work. It only shows me the difference by month and no rolling value.

     

    I also tried this from 

    http://www.daxpatterns.com/cumulative-total/

     

    but same graph is shown. Can it be because of my calendar or any other formatting issues maybe?

     

    Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

     

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago
    The auto calendar gives you the a calendar based on your minimum.and maximim date on your model in order for the YTD to.work you need to have a calendar starting in january.and ending un december I would add the calendar like this.

    Calendar =
    CALENDAR (
    DATE ( YEAR ( MIN ( Transactions[Date] ) ), 1, 1 ),
    DATE ( YEAR ( MAX ( Transactions[Date] ) ), 12, 31 )
    )

    Then try the TOTALYTD formula
  • sven7's avatar
    sven7
    Frequent Visitor
    8 years ago

    MFelix Thank you so much for help, I really appreciate your help. Unfortunately still the same issue. I have got the YTD formula, I have set the calendar, I have linked the dates from the logs file and calendar and as the visual I have selected the dates from the calendar.

     

     

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    Hi sven7,

     

    I have used your data and produced this two models below one with the YTD values and another with the calculated values as you can see both make the same result. so total YTD values sum up to total year, on the second table the rollup goes trough 2018.

     

    Can you send a print with the measures and table to check the values please.

     

    Regards,

    MFelix

  • sven7's avatar
    sven7
    Frequent Visitor
    8 years ago

    MFelix Thank you for your patience. I do not know where I make my mistake, I have used just some simple data to reproduce your table, but still the same issue. Please see here. 

     

    Hope we can find my mistake. BR Sven

     

  • sven7's avatar
    sven7
    Frequent Visitor
    8 years ago

    MFelix What a stupid mistake... All working now, thank you very much. 

     

    It really helps to visualize our data. Thanks