Visualizing the Monthly Value of an Offer Base

[Anonymous]

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

[v-xicai]

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.

[Anonymous]

Hi @v-xicai 

Thank you for your tip, but this measure does not return anything for me. The visual is blank.

Jerry

[amitchandak · Accepted solution]

@Anonymous , refer if these 2 can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[Anonymous]

Thank you for your reply and tips!