cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sven7 Frequent Visitor
Frequent Visitor

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

Hi @MFelix,

 

I used both ways, I am so far that it shows the balance or difference by each month, but not the rolling value. Also it is only showing 2017 and no previous years. 

 

Any further tips are much appreciated thank you so much for your help.

 

sample.JPGsample2.JPG

Super User III
Super User III

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

Hi @sven7,

 

My error mixup two different solutions I was working on.

 

Create the relationship between both tables and then add this measure:

Balance = 
 TOTALYTD(SUM(Money_In_Out[Amount received])-SUM(Money_In_Out[Amount taken]),'Calendar'[Date])

The other formula  without links was to make it month on month no matter the Year so the balence from december would go to January

In that case you need to use the formula:

Balance_1 = 
var dimdate = MAX('Calendar'[Date]) 
return
CALCULATE(SUM(Money_In_Out[Amount received])-SUM(Money_In_Out[Amount taken]),Money_In_Out[Date] <= dimdate)

and don't connet the tables.

 

Regards,

MFelix

 

 


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sven7 Frequent Visitor
Frequent Visitor

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

Thanks @MFelix, it is getting closer. 

 

I can see the difference for each month now, but still there is no rolling value. When I try to use the month column from the calendar table it gives me an error that it cannot be displayed. 

 

Thanks again.


BR Sven

Super User III
Super User III

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

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


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sven7 Frequent Visitor
Frequent Visitor

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

@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:

21.JPG

 

Thanks Sven

Super User III
Super User III

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

You must link the date columns but use the.month colum from calendar table on your.visual

Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sven7 Frequent Visitor
Frequent Visitor

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

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] )
    )
)

 

Super User III
Super User III

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

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

Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sven7 Frequent Visitor
Frequent Visitor

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

@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.

 

 

Super User III
Super User III

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

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.

money.png

 

Regards,

MFelix


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors