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.
Hi,
I'm trying to make a linear graph that shows the cumulative sales in April vs the actual sales forecast
First, my cumulative sales formula is:
Cumulative Sales = CALCULATE(SUM('BD-New'[Amount]); Filter(ALL('Calendar Table'); 'Calendar Table'[Date] <= MAX('Calendar Table'[Date])))
but it isn't working, i don't know if my calendar is ok..
I made a calendar using this formula
Calendar Table =
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)
maybe that's the problem..
and second, i have a forecast of X amount of usd by the end of the month, how can i insert that in the graph a way that it can be displayed as cumulative projection by day?
Thanks in advance
Solved! Go to Solution.
Hi @Raul09,
I made the assumptions below to make this owrk please tell me if any of this is incorrect:
- Slicer is in Date table
- Objectives are in a separated table
- Objectives are linked to the last day of each month
- Table Dates link to table Sales and Objectives by column date
Please do as follow
1 - In table objectives create a new column and a new measure withthe following formulas
COLUMN = Days_Month = DAY(Objective[Date]) MEASURE= Daily_Total = DIVIDE(SUM(Objective[Objective]);SUM(Objective[Days_Month]))
This will return the number of days in the month
2 - Create in Table Sales the measure below
Obj_YTD = TOTALYTD([Daily_Total]*DAY(MAX('Calendar Table'[Date]));'Calendar Table'[Date])
Add the Obj_YTD to you visual and you will have the total you need.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Raul09,
Try to work with the TOTALYTD measure it will allow to make the calculation.
Cumulative Sales = TOTALYTD(SUM('BD-New'[Amount]);'Calendar Table'[Date])
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks for the reply,
my sales cumulative is working right now, but i still need the cumulative daily goal to show along.
Let's say we have a 500k monthly goal, we are currently in April 18.. april has 30 days, so we have 12 days left.
My daily goal is 16,6k and i want to show that in the same graphic as a linear value along my actual daily sales cumulative.
how can i do that?
Thanks.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêsi need it to work with the slicer/filter MFelix
Hi @Raul09,
I made the assumptions below to make this owrk please tell me if any of this is incorrect:
- Slicer is in Date table
- Objectives are in a separated table
- Objectives are linked to the last day of each month
- Table Dates link to table Sales and Objectives by column date
Please do as follow
1 - In table objectives create a new column and a new measure withthe following formulas
COLUMN = Days_Month = DAY(Objective[Date]) MEASURE= Daily_Total = DIVIDE(SUM(Objective[Objective]);SUM(Objective[Days_Month]))
This will return the number of days in the month
2 - Create in Table Sales the measure below
Obj_YTD = TOTALYTD([Daily_Total]*DAY(MAX('Calendar Table'[Date]));'Calendar Table'[Date])
Add the Obj_YTD to you visual and you will have the total you need.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks MFelix!
- Objectives are linked to the last day of each month
i don't have this, can you elaborate any further?
Regards,
What is the data for your objectives? In my example I did it like this
Date Objective
31/01/2017 500
28/02/2017 500
31/03/2017 500
...
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsWow! is working now, thanks! really!!
but my sales cumulative stopped working since i've changed the slicer date from my sales table date to the calendar table date
The formula is:
Sales Cumulative =
CALCULATE (
SUM ('BD-NEW'[Amount]);
FILTER (
ALL (DimDate[Date]);
i need it to work with the slicer/filter too
appreciate the help!
The graphic is looking like this now, cumulative sales isn't working with the slicer/filter
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |