The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi all
I have the followeing table:
I would like to divide all returned values from the 4th column on only the sales from January
How could I achieve that ?
Thanks in advance
Regards
Solved! Go to Solution.
Hi, @Anonymous
If you try to solve it with calculated columns, it will be easy.
Sales_January =
CALCULATE (
MAX ( 'Table'[Sales] ),
FILTER (
'Table',
[Date].[MonthNo] = 1
&& [Date].[Year] = EARLIER ( 'Table'[Date].[Year] )
)
)
%Returned/January = DIVIDE([Returned],[Sales_January])
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.
Hi, @Anonymous
If you try to solve it with calculated columns, it will be easy.
Sales_January =
CALCULATE (
MAX ( 'Table'[Sales] ),
FILTER (
'Table',
[Date].[MonthNo] = 1
&& [Date].[Year] = EARLIER ( 'Table'[Date].[Year] )
)
)
%Returned/January = DIVIDE([Returned],[Sales_January])
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.
I need to divide all values on the red boxes on the value of the first month of the their year
When I apply divide (Returned, Sales January) it only divided the first month and don't divide the other months.
In OTher words, sales January is a measure where it calculate the sales when month = "January" , now I want to fill down all the missing data . in the third column ('Slaes January') so I can divided on the 4th column to calculate the ratio
@Anonymous ,
Either use an independent date slicer and select jan there
// Independent Date table - Date1 and Joined Table Date
measure =
var _max = maxx(allselected('Date1'),'Date1'[Date]) // Use Date(2021,01,31)
var _min = minx(allselected('Date1'),'Date1'[Date])
return
CALCULATE(SUM(Sales[Sales Amount]),filter(Date, Date[Date]>=_min && Date[Date] <=_max ))
or put date
measure =
var _max = Date(2021,01,31)
var _min = Date(2021,01,01)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(Date, Date[Date]>=_min && Date[Date] <=_max ))
You can also Table's Date in place Date[Date]
Thank you for the fast reply
I think I misexplained my problem
I want to divide all the months returned value on the sales resulted from first month of the year
The idea filling all the values in yellow so I can build monthly trend
Thanks again and looking forward for your help
REgards
@Anonymous , Use all in filter and try
measure =
var _max = Date(2021,01,31)
var _min = Date(2021,01,01)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(Date) , Date[Date]>=_min && Date[Date] <=_max ))
Unfortunately its not working
I need to return the value of sales for the first month of the year. this value should be a reference value to divide all other values on it
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
95 | |
80 | |
62 | |
56 |
User | Count |
---|---|
252 | |
121 | |
112 | |
81 | |
70 |