Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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]
)
)
Hi,
I believe this is the solution you want. You may download my PBI file from here.
Hi,
I believe this is the solution you want. You may download my PBI file from here.
@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
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]
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |