Reports For LEIs renewal for total and month & year

[kousar99]

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

[kousar99]

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

[v-yiruan-msft · Accepted 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

[amitchandak]

@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.