Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi ,
As I was confused about what kinda reports or charts can be used for leis renewal summary for total and month & year on bi desktop. below is the output.
Total | Year_Month |
2135 | 2020-07 |
1968 | 2020-09 |
1468 | 2020-08 |
Thanks in Advance
Solved! Go to Solution.
Hi @kousar99 ,
Based on the information you provided it seems that the data source you are connecting to is SQL Server, you can refer to the official documentation below to connect to your SQL Server data source in Power BI Desktop.
Connect to SQL Server database from Power Query Desktop
After successfully connecting to your sql server data source, you can refer to the following steps to get the year-month and total values. Please find the details in the attachment.
1. Create the following calculated column to get the year-month
Year_Month = FORMAT('Table'[nextrenewaldate],"YYYY-MM")
2. Create the following measure to get the total
Total =
CALCULATE (
COUNT ( 'Table'[item] ),
FILTER (
'Table',
'Table'[managingglou] = "EVK05KS7XY1DEII"
&& 'Table'[initialregistrationdate] < DATE ( 2019, 07, 01 )
&& 'Table'[Year_Month] IN { "2020-07", "2020-08", "2020-09" }
)
)
Best Regards
Hi,
for the below tabled output i have written the query and i need to make report for the query
given with output and build reprt in power bi desktop.
If possible could you help me how can i write dax for the respective query
select count(*) as Total,substr(nextrenewaldate,1,7) as year_month
from lei_user.gleif_leis_v21
where managingglou='EVK05KS7XY1DEII'
AND substr(initialregistrationdate,1,7)<'2019-07'
AND substr(nextrenewaldate,1,7) IN('2020-07','2020-08','2020-09')
Group by substr(nextrenewaldate,1,7);
Total | Year_Month |
2135 | 2020-07 |
1968 | 2020-09 |
1468 | 2020-08 |
Hi @kousar99 ,
Based on the information you provided it seems that the data source you are connecting to is SQL Server, you can refer to the official documentation below to connect to your SQL Server data source in Power BI Desktop.
Connect to SQL Server database from Power Query Desktop
After successfully connecting to your sql server data source, you can refer to the following steps to get the year-month and total values. Please find the details in the attachment.
1. Create the following calculated column to get the year-month
Year_Month = FORMAT('Table'[nextrenewaldate],"YYYY-MM")
2. Create the following measure to get the total
Total =
CALCULATE (
COUNT ( 'Table'[item] ),
FILTER (
'Table',
'Table'[managingglou] = "EVK05KS7XY1DEII"
&& 'Table'[initialregistrationdate] < DATE ( 2019, 07, 01 )
&& 'Table'[Year_Month] IN { "2020-07", "2020-08", "2020-09" }
)
)
Best Regards
@kousar99 , Your need is not clear. You can use Bar, Line, Pie, Table to Matrix visual
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |