Break a monthly figure into a daily figure

[spitfyah]

Hi all,

I currently have a target table as this:

Client	Start Date	End Date	Target 1	Target 2	Target 3
Client 1	01-Jan-22	31-Jan-22	223	709	343
Client 2	01-Jan-22	31-Jan-22	674	467	47
Client 3	01-Jan-22	31-Jan-22	264	379	561
Client 1	01-Feb-22	28-Feb-22	118	22	881
Client 2	01-Feb-22	28-Feb-22	607	495	356
Client 3	01-Feb-22	28-Feb-22	333	61	15
Client 1	01-Mar-22	31-Mar-22	447	859	100
Client 2	01-Mar-22	31-Mar-22	736	56	684
Client 3	01-Mar-22	31-Mar-22	216	619	663
Client 1	01-Apr-22	30-Apr-22	309	666	331
Client 2	01-Apr-22	30-Apr-22	217	555	143
Client 3	01-Apr-22	30-Apr-22	870	280	130
Client 1	01-May-22	31-May-22	651	820	610
Client 2	01-May-22	31-May-22	899	622	835
Client 3	01-May-22	31-May-22	895	651	873

I wanted to make a matrix that would show something like this

	MTD Target	CTD Target	Rolling 3M Target
Client 1	420	1294	1213.929
Client 2	580	2140	1728.107
Client 3	577.4193548	1996.419	1770.455

MTD Target would be for the 1st 20 days of may, target figure/(days in month)*days elapsed in month

CTD Target is everything from February 1st onwards as that's when the contract starts

Rolling 3M Target would be the target for the last 90 days (so from Feb 20 to May 20)

I only have the monthly figures so was hoping to get a measure that would split the monthly figure out via the days present in the month. If possible I would also like a generic target figure that would show the target based on the date filters I have applied in the slicers.

[Anonymous]

Hi @spitfyah 

Please have a try.

MTD Target =
VAR _month =
    MONTH ( MAX ( 'Table'[Start Date] ) )
VAR _days =
    DAY ( EOMONTH ( MAX ( 'Table'[Start Date] ), 0 ) )
VAR _sum =
    CALCULATE (
        MAX ( 'Table'[Target 1] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[client] = SELECTEDVALUE ( 'Table'[client] )
                && 'Table'[Start Date] = SELECTEDVALUE ( 'Table'[Start Date] )
        )
    )
VAR _datt =
    DAY ( DATE ( 2022, 5, 20 ) )
RETURN
    _sum / _days * 20

How to calculate the other columns?

Could you please provide more details?  The specific calculation of the formula. Like the MTD Target: sum target1 in May / days in each month * Number of days elapsed during the month

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@spitfyah , refer if this can help

Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...