Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

The Right Chart with counts and change

Hi, I need help with graph.

 

I have counts of customers
start count in Januray = 5478 and change in January = +1562
start count in February = (5478+1562) = 7040 and change in February = +37
start count in March = (7040+37) = 7077 and change in March = +173
.... etc ...
start count in December = 7683 and change in December = -1062

and problem is, that I want to see the final count = 6621 with decreasing line in chart (red pen in chart).
Is it possible?

Thanks

 

image.png

5 REPLIES 5
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

That's possible. But the problem is the line values are inconsistent. The solution could be checking the last month of the X-axis and adding one more change. 

Can you share a sample, please? Then we can work out a solution in details. 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

It contains numbers of my customer, so sorry, I can't publish it.
But I can explain each part of computation or structure.

For example:
I have fact table with "points" and dates of their validity. e.g. Point "A" and date_validity_from and date_validity_to. Point "B" date_validity_from and date_validity_to etc.
Count_Date_Start (line chart) is computing value for count of points at the beginning of the period. The period is choosing from filter. Nowadays, there is selected period from 01/2018 to 12/2018.
Count_Change (bar chart) is computing value for count of points as a result between count in End and count in Start (Count_Date_End - Count_Date_Start).

Do you have some specific question still?

I know, that Power BI shows the right values in my chart. But I think that I need to see Count_Date_Start in line chart for "january 2019" too, but without january 2019. So there must be some trick for it. But I don't have a idea.

Thank you

Hi @Anonymous,

 

You mean you don't have the data of January 2019. Right? At least we need the January 2019 in the Calendar. Since your visual already works, I think it will work if you have the date January 2019. 

Can you share a dummy sample? At least the formulas you used.

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I have data of January 2019. I know all of future data, because each point have date_validity_from and date_validity_to, which may be valid to the future (from 1.1.2015 to 31.12.20122).

I think my problem is with presentation of data. User chose a period from 01/2018 to 12/2018 for 12 months but I want to see 12 values in bar chart (for change) and 13 values for line chart (for count) in the same chart.

 

The value "6621" is thirteenth value, which I need to see in chart. It is Count_Date_End in 31.12.2018 or Count_Date_Start in 1.1.2019

 

Formulas are:

 

Count_Change = DWH_FCT_POD[Count_Date_End] - DWH_FCT_POD[Count_Date_Start]
 
Count_Date_Start =
CALCULATE(
DISTINCTCOUNT ( DWH_FCT_POD[POD_ID] ) ;
GENERATE ( VALUES ( DWH_DIM_DATE_POD[PREVIOUS_DAY]);
FILTER ( DWH_FCT_POD;
CONTAINS ( DATESBETWEEN ( DWH_DIM_DATE_POD[PREVIOUS_DAY];
DWH_FCT_POD[POD_FROM];
DWH_FCT_POD[POD_TO_F]
);
DWH_DIM_DATE_POD[PREVIOUS_DAY];
MIN(DWH_DIM_DATE_POD[PREVIOUS_DAY])
))))

 

 

Count_Date_End =
CALCULATE(
DISTINCTCOUNT ( DWH_FCT_POD[POD_ID] ) ;
GENERATE ( VALUES ( DWH_DIM_DATE_POD[DATE_ID]);
FILTER ( DWH_FCT_POD;
CONTAINS ( DATESBETWEEN ( DWH_DIM_DATE_POD[DATE_ID];
DWH_FCT_POD[POD_FROM];
DWH_FCT_POD[POD_TO]
);
DWH_DIM_DATE_POD[DATE_ID];
MAX(DWH_DIM_DATE_POD[DATE_ID])
)
&& DWH_FCT_POD[POD_TO] <> MAX(DWH_DIM_DATE_POD[DATE_ID])
)))

Hi @Anonymous,

 

The period is chosen by users. Usually, it's a slicer. I'm afraid we can't add one more month. If we respect the slicer, there isn't a month Jan 2019. If we ignore the slicer by ALL, the measure will go through all the months. It can't add just one month.

Maybe some experts have some other tricks.

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.