Budget Value Quarter to Month

[unais]

Dear Team,

Please refer below link and Advise best solution for Budget Value Quarter to Month wise 

Budget Table with Quarter Value.docx

[Fowmy]

@unais 

Go to Modeling Tab and click New Table and paste the code below and you will get a new table with amounts split into months for each line

Motnhly Budget = 
SELECTCOLUMNS(
	FILTER(
		CROSSJOIN(
			Table4,
		    	ADDCOLUMNS(
				FILTER(
				 	CALENDAR(Min(Table4[BDate]), EDATE(MAX(Table4[BDate]),2)),
				 	[Date] = EOMONTH([Date],-1) + 1
		 		),
		 		"Q", ROUNDUP(MONTH([Date])/3,0)
				)
		),
		ROUNDUP(MONTH(Table4[BDate])/3,0)= [Q]
	),
	"GL Group", Table4[GLGroup],
	"Profit Center", Table4[Profitcenter],
	"Date", [Date],
	"Monthly Amount", DIVIDE( Table4[Amount] , 3 )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[unais]

@Fowmy 

based below table

Motnhly Budget =

SELECTCOLUMNS(

    FILTER(

        CROSSJOIN(

            GLGroupBudget,

             ADDCOLUMNS(

                FILTER(

                    CALENDAR(Min(GLGroupBudget[BDate]), EDATE(MAX(GLGroupBudget[BDate]),2)),

                    [Date] = EOMONTH([Date],-1) + 1

                ),

                "Q", ROUNDUP(MONTH([Date])/3,0)

                )

        ),

        ROUNDUP(MONTH(GLGroupBudget[BDate])/3,0)= [Q]

    ),

    "GL Group", GLGroupBudget[GLGroup],

    "Profit Center", GLGroupBudget[Profitcenter],

    "Date", [Date],

    "Monthly Amount", DIVIDE( GLGroupBudget[Amount] , 3 )

)

output 

 Output Repeating Day  Value instead of Month

[Fowmy · Accepted solution]

@unais 

I changed the Date to Month-Year

 Motnhly Budget =
SELECTCOLUMNS(
    FILTER(
        CROSSJOIN(
            GLGroupBudget,
             ADDCOLUMNS(
                FILTER(
                    CALENDAR(Min(GLGroupBudget[BDate]), EDATE(MAX(GLGroupBudget[BDate]),2)),
                    [Date] = EOMONTH([Date],-1) + 1
                ),
                "Q", ROUNDUP(MONTH([Date])/3,0)
                )
        ),
        ROUNDUP(MONTH(GLGroupBudget[BDate])/3,0)= [Q]
    ),
    "GL Group", GLGroupBudget[GLGroup],
    "Profit Center", GLGroupBudget[Profitcenter],
    "Date", FORMAT([Date],"Mmm YYYY"),
    "Monthly Amount", DIVIDE( GLGroupBudget[Amount] , 3 )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn