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.
Hello guys,
I want to build a measure to show the revenues per month for each year. I have done that, but they should be summarized each month.
I have two examples for that, how it should look like. So all I need is a TotalYTD function for the year 2018 and 2017 I guess.
Thanks in advance!
Greetings,
Christopher
Solved! Go to Solution.
hi, @Anonymous
After my research, you could try this way:
Step1:
Add a year and month column for date column
Step2:
Use this formula to create a TotalYTD Measure
Measure = CALCULATE(SUM(Table1[Qty]),FILTER(ALL(Table1[Month]),Table1[Month]<=MAX(Table1[Month])))
Step3:
Drag month field into X-Axis and Year field into Legend
Result:
here is pbix file, please try it.
Best Regards,
Lin
hi, @Anonymous
After my research, you could try this way:
Step1:
Add a year and month column for date column
Step2:
Use this formula to create a TotalYTD Measure
Measure = CALCULATE(SUM(Table1[Qty]),FILTER(ALL(Table1[Month]),Table1[Month]<=MAX(Table1[Month])))
Step3:
Drag month field into X-Axis and Year field into Legend
Result:
here is pbix file, please try it.
Best Regards,
Lin
Is there an option to cut the graph in 2019 for the months in the future?
So when we have February 2019, the graph should only show the two months, actually it goes to linear line over the future months.
That works perfectly, thank you very much!!
Try setting your months on the axis and your year in the legend.
Hi @jvangrunderbeec,
thanks for your fast reply, but when I will do that, I have the problem that I only see the year 2019 with the TotalYTD function. When I choose only the revenue as the graph instead of the TotalYTD function, I will have the same like in the first screenshot.
Try this formula:
Cumulative Revenue =
VAR CurrDate =
MAX ( DateTable[Date] ) //Insert datetable date column
Var CalculateddMeasure =
CALCULATE(sum(Revenue)) //Insert to calculate number
RETURN
//Total
IF (
ISBLANK ( CalculateddMeasure );
BLANK ();
IF (
COUNTROWS ( FactTable ) > 0; //Insert FactTable name
CALCULATE (
CalculateddMeasure;
FILTER (
ALLSELECTED ( DateTable ); //Insert Date Table name
DateTable[Date] <= CurrDate //Insert Date Table Date column
&& YEAR ( DateTable[Date] ) = YEAR ( CurrDate ) //Insert Date table Date column
)
);
BLANK ()
)
)
)
@Anonymous
Are you sure then that the number is correct? isn't is counting all your date? So it says 2019 but it is for all the yaers
The first one is how it is actually, the screenshot below is how it should be (revenues are summarized). And there will compare the different years.
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
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 |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |