Adding a YTD column after the dynamic month column in matrix visual

[niteshtrehan89]

Hi All, 

Need a quick help to in matrix visual.

Data is below 

Category        MIS Date       Sales

A                    1-1-2024        100

A                    2-1-2024         200

A                    3-1-2024         300

when i am taking the matrix visual, then 

               Jan                Feb             Mar 

A             100               200             300            

need to add a dynamic column of forecast inside the visual. Any work around for the same.

final Output will be 

               Jan                Feb             Mar        Forecast      YTD

A             100               200             300            800          600

[niteshtrehan89]

Hi @rajendraongole1 , 

thanks for your reply but this not the solution I am looking. 

I need first all the months (Jan to Dec) then cummulative YTD the cummulative forecast in a matrix visual. 

final Output will be 

Category    Jan   Feb   Mar   Apr   May   Jun   Jul   Aug  Sep   Oct  Nov  Dec  YTD     Forecast

     A            10     20     30     40     50      60    70    80    90     100  110  120   780         900

[Anonymous]

Hi, @niteshtrehan89 

How is Forecast calculated?

Best Regards,

Community Support Team _Charlotte

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

[niteshtrehan89]

Hi @Anonymous ,

thanks for the message.

Forecast calculation is not in important. I am loooking how it will be put inside the matrix visual as the dynamic column.  

[Anonymous · Accepted solution]

Hi, @niteshtrehan89 

You can try to control the output of column total by calculating it as a dynamic column, but with only one column.

An example. Swipe the year-to-date total.

Measure = 
Var _Sum=CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),[MIS Date]>=DATE(YEAR(TODAY()),1,1)&&[MIS Date]<=TODAY()))
RETURN
IF(HASONEVALUE('Table'[MIS Date].[Month]),SUM('Table'[Sales]),_Sum)

Rename:

Result:

I hope this provides you with ideas.

Best Regards,

Community Support Team _Charlotte

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

[rajendraongole1]

Hi @niteshtrehan89 - create a measure for Year-to-Date (YTD) sales.

YTD Sales =

TOTALYTD(

    SUM('maxt'[Sales]),

    'maxt'[misdate],

    ALL('maxt')

)

another measure for forcast

Forecast Sales =

VAR LatestMonth = MONTH(MAX('maxt'[misdate]))

VAR ActualSales = SUM('maxt'[Sales])

VAR ForecastMultiplier = 12 / LatestMonth

RETURN

ActualSales * ForecastMultiplier

output is above for ytd and forcast . please check 

Hope this works