DAX to ignore month division in matrix

[moliveira-GA]

Hello guys,

Please, i need a help to create a measure of a full year forecast, lets say, 'Sales Revenue', in a month divided matrix but showing de full year sum ignoring the month division. 

To each month we have a new version of forecast, which leads to a new Full year's total so, we need to see how much the full year's forecast was changed between months. Problems is, i couldn't bypass the month column division:

		Jan	Fev	Mar	Abr	...	Full year
1	Sales Rev.	1000	1000	1000	1000	11000	15000
2	FY Fcst	12000	12000	12000	12000	12000
3	FY Fcst v2		13000	13000	13000	13000
4	FY Fcst v3				15000	15000

So, as you can see on table above, we have a full year's forecast of 15K. But, in the first version of forecast, we had 12K (line 2), in the version 2, we have a new customer, so our full year's forecast was increased by 1K, reaching 13K. 

I don't if i made myself clear 🙂

I was trying to use:

Calculate(Sum([value],ForecastVersion[###],ALL(calendar[date])

but this DAX continue to consider the month division in the matrix, so i got the total Month value. 

Thanks in advance.

[amitchandak]

@moliveira-GA , if you dates then you can use MTD and ytd

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

this month Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))

refer a different approach here closingbalancemonth ,closingbalancequarter, closingbalanceyear  - https://youtu.be/yPQ9UV37LOU

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[moliveira-GA]

@amitchandak 

Thanks my friend. 

I'm going to try that way too. But I have a doubt: to reach the total year, is really necessary to calculate the MTD Sales? that part is not clear to me, but i understand that it could be only a tip for an eventual situation. that's it?

[Greg_Deckler]

@moliveira-GA - It's difficult to say but you are on the right track with ALL or ALLEXCEPT. But, overall, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[moliveira-GA]

Thanks for your kindly answer. 

I couldn't find the proper result from the page you sugested, but i managed to find a way to do what i want. Just for you know, considering that you could have a better way to do it, here is my sample data and expected results:

https://1drv.ms/u/s!Ake8j9HjtUSWqXiFQLoeN0FM-5sJ?e=jX1M4z

The way i found to do:

TOTALYTD([(R+F)_SalesRev],ALL(f_Calendario[Date])

the only problem that still persists is that I can't filter by year, because of 'ALL'. There is a way to filter by year (which is in a slicer) but not filter by month in the matrix?
 
The measure '(R+F_SalesRev)' calculate a sum between the actual sales and how much rests in the last version of the forecast, using a simple Calculate(Sum()) with filters and:

RestanteForecast_SalesRev =

VAR LASTDATESALES =

CALCULATE(

MAX(b_MovimentoCtaCC[Data]),

ALL(b_MovimentoCtaCC[Valor])

)

VAR RESULT =

CALCULATE(

[SalesRev_Fcst],

KEEPFILTERS(f_Calendario[Date]>LASTDATESALES)

)

RETURN

RESULT

to identify the rest of forecast.