distributing values for each month with yearly comparison

[JimJim]

Hi,

Really struggling with something that I've been at for the past few days.

I have a measure that distributes values between a start date and end date for each month

pipeline_current_year =

    CALCULATE(SUMX(SUMMARIZE(FILTER(CROSSJOIN('pipeline','date'),

        'date'[date] >= pipeline[start_date] &&

        'date'[date]<= pipeline[end_date]),

        pipeline[opportunity_number],'date'[date],pipeline[total_value_usd],

        pipeline[start_date],pipeline[end_date]),

        DIVIDE(pipeline[total_value_usd],DATEDIFF(pipeline[start_date],pipeline[end_date],day)+1)))

I also have a similar query that looks at a different table to get the previous years values

pipeline_previous_year =

 CALCULATE(SUMX(SUMMARIZE(FILTER(CROSSJOIN('pipeline_py','date'),

        'date'[date] >= pipeline_py[start_date] &&

        'date'[date] <= pipeline_py[end_date]),

        pipeline_py[opportunity_number],'date'[date],pipeline_py[total_value_usd],

        pipeline_py[start_date],pipeline_py[end_date]),

        DIVIDE(pipeline_py[total_value_usd],DATEDIFF(pipeline_py[start_date],pipeline_py[end_date],day)+1)))

Both measures work and return the correct data, but I have a disconnected date table and really struggling to compare current year v previous year on a clustered chart

The value for Dec 2022 needs to shift one year forward to Dec 2023, so that I can compare.

I would really appreciate any help with this. Link to report attached.

distributed pipeline.pbix

[Ashish_Mathur]

Hi,

This approach would solve the problem but would bloat the table.

1. In the Query Editor, Append the 2 pipeline tables
2. Using rh technique described in this link, create one row for each month between the start and end date.  The end result would be a single table with Opportunity_number, Date and total_value_usd
3. Now create a relationship from the Date column  of the table rederred to in Table 2 above to the Calendar Table.
4. Now write these measures

Total = sum('Final table'[Total_value_usd])

Total in PY = calculate([total],previousyear(calendar[date]))

Hope this helps.

[JimJim]

Thanks, will try this later and feedback

[lbendlin]

Please explain why you would spread the pipeline value out like this.  Isn't it sufficient to filter by opportunity start and end date?

You may also want to consider pre-appending your raw data tables.

[JimJim]

Thank you, taking the time to see if this will work for me