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.
Hey PBI pro's,
I'm a complete newbie with PowerBi (more familiar with python and R) and I am trying to create a report for our sales managers to follow the total amount of our offers we have made and how big our offer base is money wise. The problem is I am trying to visualize a 24-month trend of our offers, so how do I create a measure that will visualize the amount per month?
The data looks something as follows:
Client | Offer Amount | Offer Submition Date | Offer Accepted/Rejected Date |
Barley's | 200€ | 01.02.2020 | 31.03.2020 |
Mickie Dees | 1000€ | 10.03.2020 | |
Burger Queen | 2000€ | 10.04.2020 | 30.06.2020 |
So from this data, how would I create a visualization that shows the total amount of offers in February is 200€, March is 1200€, In April/May and June it's 3000€ and in July it's 1000€ again? This is an issue with two date dimensions I think, but as I am new to Power Bi I have no idea how to solve it!
Thanks in advance,
Jeppe
Solved! Go to Solution.
@Anonymous , refer if these 2 can help
https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0
Hi @Anonymous ,
You may create calendar table firstly, then create measure like DAX below.
Table:
Calendar=CALENDARAUTO()
Measure:
Offer Distribution =
VAR _Date =
SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
CALCULATE (
SUM ( Table1[Offer Amount] ),
FILTER (
Table1,
Table1[Offer Submition Date] <= _Date
&& _Date <= Table1[Offer Accepted/Rejected Date]
)
)
You may choose Clustered Column chart to display the result, put 'Calendar'[Date] into X axis box of chart visual, and put [Offer Distribution] into Values box.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xicai
Thank you for your tip, but this measure does not return anything for me. The visual is blank.
Jerry
@Anonymous , refer if these 2 can help
https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0
Thank you for your reply and tips!
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |