The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
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 |
---|---|
141 | |
109 | |
107 | |
75 | |
61 |
User | Count |
---|---|
276 | |
129 | |
122 | |
100 | |
88 |