Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Muthuramalingam
Frequent Visitor

Dax Calculation to show current year and variance data in a single measure

Hi

 

We are preparing an indirect method of the cash flow statement from the general ledger data.  I will insert the table image of general ledger data and cash flow mapping. 

 

General ledger data sample image 

Muthuramalingam_0-1667558231419.png

Cash flow mapping image

Muthuramalingam_1-1667558287424.png

 

 

In cashflow mapping, we have mentioned the direct and indirect connections of financial nodes. 

 

We need to show current year data to DIRECT cashflow bucket list items and also we need to show Variance (Current year- last year) for INDIRECT cashflow bucket lists item.  In the cash flow statement in a single column, we need to show current year and variance measure in a single column by using a dax measure.

 

Direct Connection                                                                  

Dividend Income                                                                   

Amortization of software                                                       

Depreciation

Bad debt expenses

 

Indirect Connection

Increase (decrease) in provision for bonuses

Increase (decrease) in provision for bonuses for directors (and other officers)

Increase (decrease) in provision for retirement benefits for directors (and other officers)

Increase (decrease) in provision for retirement benefits

Increase (decrease) in provision for retirement benefits

Increase (decrease) in provision for retirement benefits for directors (and other officers)

Increase (decrease) in provision for loss on guarantees

Increase (decrease) in provision for point card certificates

 

Muthuramalingam_3-1667559236324.png

 

 

Relationship image

Muthuramalingam_2-1667558467862.png

 

In the cash flow statement in a single column, we need to show the current year and variance measure in a single column by using a dax measure.

 

Community members, please suggest a way to solve this issue. 

1 ACCEPTED SOLUTION

Hi @Muthuramalingam ,

 

You can try this method:

Notice that your current and variance are both measures, so I create the data like this:

vyinliwmsft_0-1667895124145.png

New measure:

Current = SUM('Table'[Current Year])
var = SUM('Table'[Variance])
 
The two measures above are simulated the measure you have.
The only measure you need to create by yourself is below:
 
Result = IF(MAX('Table'[Type]) = "Direct", [Current], [var])
 
The result is:
vyinliwmsft_2-1667896402162.png

 


 

Is this what you expect? Hope this helps you.

Here is my PBIX file.

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Muthuramalingam
Frequent Visitor

Hi @daXtreme 

 

Muthuramalingam_0-1667752905341.png

Understood. Thanks for your reply.I will explain the issue shortly. Please go through the image. Financial accounts are in a single column and we mentioned the types of the accounts. For direct accounts, we want to show the current year data and for indirect accounts, we want to show the variance data. We need to show both data in a single column by using a single measure.

 

 

 

Hi @Muthuramalingam ,

 

You can try this method:

Notice that your current and variance are both measures, so I create the data like this:

vyinliwmsft_0-1667895124145.png

New measure:

Current = SUM('Table'[Current Year])
var = SUM('Table'[Variance])
 
The two measures above are simulated the measure you have.
The only measure you need to create by yourself is below:
 
Result = IF(MAX('Table'[Type]) = "Direct", [Current], [var])
 
The result is:
vyinliwmsft_2-1667896402162.png

 


 

Is this what you expect? Hope this helps you.

Here is my PBIX file.

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yinliw-msft 

Thank you for your response. I have tried your dax its working perfectly.

OK, should this not be as easy as creating a measure that displays different numbers utilizing conditional logic via IF or SWITCH? The branching should be based on the type of the account...

daXtreme
Solution Sage
Solution Sage

Hi @Muthuramalingam 

A bit too much to read, I guess. Can you not break this down into smaller, more digestible chunks, please? As it currently stands, I have serious doubts someone will be willing to dissect this... but might be wrong 🙂

Also, if I were you, I'd place a link to a file with sample data that demonstrates the issue. Otherwise, it'll be hard to deal with this, I suppose.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.