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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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]

                )

)

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

 

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

 

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

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.
Anonymous
Not applicable

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]

                )

)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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