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

Balance Sheet vs Revenue YTD / QTD / Monthly

Hi,

 

I have a data set that captures financial data for the last 6 financial year. For each produt I have one line per financial month end period, along with the balance sheet value, and revenue data. As an example the data looks something like this:

 

Product             Financial Year         Period            Balance Sheet            Revenue

Product 1          2012                       Jan 2012           10                             1

Product 1          2012                       Feb 2012          11                             3

Product 1          2013                       March 2012      15                             -2

 

And so on.... 

 

What I am trying to do within PowerBI is to create one page where the users can use filters to view the data by Financial year and month end date, however in a few different ways:

 

YTD

QTD

Monthly

 

Essentially, I want filters for them to choose the year and month end they are interested in, and then choose how they want to see the information.

 

Where I am tripping up is that for Balance Sheet data you only ever want to show the value as at the specific month chosen, however the revenue will be cummulative YTD / QTD / Monthly up until the month selected within the particular financial year.

 

In the exmaple, if the Choose Feb 2012, Balance sheet value should show 11, but YTD revenue should show 4. (1 + 3) Similarly for March 2012 Balance sheet should show 15 and YTD revenue should show 2 (1+3 - 2) or monthly revenue should show -2.

Does anybody have any advice on how to achieve this?

2 ACCEPTED SOLUTIONS

Accepted Solutions
bpretorius Frequent Visitor
Frequent Visitor

Re: Balance Sheet vs Revenue YTD / QTD / Monthly

Thank you for the link. I have read through the post, but it does not quite solve what I need it to do.

 

I think I have managed to get around it with the following:

 

* Created measures for YTD, QTD and MTD as per normal

* Created a data structure that lists the values "YTD", QTD" and "MTD" in a new table and not relating it to anything else

* Created a new master measure with DAX:

 

[Master Measure] = IF ( HASONEVALUE (‘Period’[Period]),

                SWITCH ( VALUES (‘Period’[Period]),

                                “YTD”, [YTD Measure],

                                “MTD”, [MTD Measure],

                                “QTD”, [QTD Measure]

                )

)

Super User
Super User

Re: Balance Sheet vs Revenue YTD / QTD / Monthly

Hi,

 

I believe this is the solution you want.  You may download my PBI file from here.

 

Untitled.png

3 REPLIES 3
Community Support Team
Community Support Team

Re: Balance Sheet vs Revenue YTD / QTD / Monthly

@bpretorius,

 

You may take a good look at this discussion.

http://community.powerbi.com/t5/Desktop/Relational-Date-Filter-By-Other-Date-How-To/td-p/298373

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bpretorius Frequent Visitor
Frequent Visitor

Re: Balance Sheet vs Revenue YTD / QTD / Monthly

Thank you for the link. I have read through the post, but it does not quite solve what I need it to do.

 

I think I have managed to get around it with the following:

 

* Created measures for YTD, QTD and MTD as per normal

* Created a data structure that lists the values "YTD", QTD" and "MTD" in a new table and not relating it to anything else

* Created a new master measure with DAX:

 

[Master Measure] = IF ( HASONEVALUE (‘Period’[Period]),

                SWITCH ( VALUES (‘Period’[Period]),

                                “YTD”, [YTD Measure],

                                “MTD”, [MTD Measure],

                                “QTD”, [QTD Measure]

                )

)

Super User
Super User

Re: Balance Sheet vs Revenue YTD / QTD / Monthly

Hi,

 

I believe this is the solution you want.  You may download my PBI file from here.

 

Untitled.png

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 342 members 3,911 guests
Please welcome our newest community members: