Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear Everyone,
I am trying to apply the values from the most recent month from Sales Table by products and to apply the same values for the future months.
I have Sales table with Sales [Dates] and connected with Dates [Dates] for applying current and future periods.
As Sales table is updated with new month values the measures should again update the figures for future months.
Any help is highly appreciated!
Solved! Go to Solution.
Hi @ngrulovic ,
If so,you have to delete the relationship between the two tables;
Then create a measure as below:
Measure =
VAR _maxdate =
MAXX ( ALL ( 'Sales Table' ), 'Sales Table'[Sales Date] )
RETURN
IF (
MONTH ( MAX ( 'Dates'[Period] ) ) > MONTH ( _maxdate ),
CALCULATE (
SUM ( 'Sales Table'[Sales Amount] ),
FILTER (
'Sales Table',
MONTH ( 'Sales Table'[Sales Date] ) = MONTH ( _maxdate )
&& YEAR ( 'Sales Table'[Sales Date] ) = YEAR ( _maxdate )
)
),
CALCULATE (
SUM ( 'Sales Table'[Sales Amount] ),
FILTER (
'Sales Table',
YEAR ( 'Sales Table'[Sales Date] ) = YEAR ( MAX ( 'Dates'[Period] ) )
&& MONTH ( 'Sales Table'[Sales Date] ) = MONTH ( MAX ( 'Dates'[Period] ) )
)
)
)
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @ngrulovic ,
Glad to help.
Would you pls check my signature?🙂
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
MTD Max=
var _today = maxx(allselected('Sales'), 'Sales'[sales date])
var _min = eomonth(_today ,-1)+1
var _max = _today
return
CALCULATE(sum('Sales'[Sales Amounr]), FILTER(ALL('order'),'order'[Date] >= _min && 'order'[Date] <=_max ) )
Try measures like
final measure =
var _today = maxx(allselected('Sales'), 'Sales'[sales date])
return
if(isblank([MTD Sales]) && Max(Date[Date]) =_today , [MTD Max],[MTD Sales])
Dear @amitchandak ,
Thank you for quick reply ! Just to clarify 'Order' table and Order[Date]. Where these should come from ?
I have Sales and Dates table with columns [Date]. I am not sure about Order. Did you mean Dates table and [Date] column? I have applied this but I still get the table with only the last month figures. It does not show future months even though the time slicer is set with future months included....
Hi @ngrulovic,
Could you pls provide some dummy data with expected output for test?
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hello Kelly,
Here is the link below with the sample file. I have the most recent data of sales ending with July 2021. I would like to replicate the same values from the month of July to the future months connected with the Dates table.
https://drive.google.com/file/d/1TJtor3ppAPirpTbbIX7Hh0dQrxtzRmmG/view?usp=sharing
Hi @ngrulovic ,
If so,you have to delete the relationship between the two tables;
Then create a measure as below:
Measure =
VAR _maxdate =
MAXX ( ALL ( 'Sales Table' ), 'Sales Table'[Sales Date] )
RETURN
IF (
MONTH ( MAX ( 'Dates'[Period] ) ) > MONTH ( _maxdate ),
CALCULATE (
SUM ( 'Sales Table'[Sales Amount] ),
FILTER (
'Sales Table',
MONTH ( 'Sales Table'[Sales Date] ) = MONTH ( _maxdate )
&& YEAR ( 'Sales Table'[Sales Date] ) = YEAR ( _maxdate )
)
),
CALCULATE (
SUM ( 'Sales Table'[Sales Amount] ),
FILTER (
'Sales Table',
YEAR ( 'Sales Table'[Sales Date] ) = YEAR ( MAX ( 'Dates'[Period] ) )
&& MONTH ( 'Sales Table'[Sales Date] ) = MONTH ( MAX ( 'Dates'[Period] ) )
)
)
)
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hello Kelly,
Yes it works ! Thank you very much !
Hi @ngrulovic ,
Glad to help.
Would you pls check my signature?🙂
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |