Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
alikemalaydin
Frequent Visitor

How can I find empty dates?

‎03-15-2022 12:40 AM

Hi All,

 

Each section shows an invoice start date and end date.

Some may coincide with the same periods.

I wanna find the green empty section.

April and May 2020 are empty.

 

My purpose is how many months are empty between the first date and the last date of the customer with this ID number.

And how many months are filled between the first date and the last date?

 

For Exp below: Empty:2 Filled:70 Total: 72  and 70/72=%97

 

Many thanks.

 

alikemalaydin_0-1647328205205.png

 

Database table example. 

 

Customer IDInvoice NoStart DateEnd Date
11242200011.01.201731.12.2017
11242200021.01.201831.03.2018
11242200031.01.201831.12.2018
11242200041.03.201831.05.2018
11242200051.06.201831.08.2018
11242200061.07.201830.09.2018
11242200071.08.201831.10.2018
11242200081.09.201830.11.2018
11242200091.12.201828.02.2019
11242200101.12.201828.02.2019
11242200111.01.201931.03.2019
11242200121.01.201931.03.2019
11242200131.01.201931.12.2019
11242200141.03.201931.05.2019
11242200151.03.201931.05.2019
11242200161.04.201930.06.2019
11242200171.04.201930.06.2019
11242200181.04.201930.06.2019
11242200191.05.201931.07.2019
11242200201.07.201930.09.2019
11242200211.07.201930.09.2019
11242200221.07.201930.09.2019
11242200231.07.201930.09.2019
11242200241.10.201931.12.2019
11242200251.10.201931.12.2019
11242200261.10.201931.12.2019
11242200271.10.201931.12.2019
11242200281.11.201931.01.2020
11242200291.01.202031.03.2020
11242200301.01.202031.03.2020
11242200311.06.202031.08.2020
11242200321.06.202031.08.2020
11242200331.07.202030.09.2020
11242200341.08.202031.10.2020
11242200351.09.202030.11.2020
11242200361.09.202030.11.2020
11242200371.09.202030.11.2020
11242200381.10.202031.12.2020
11242200391.12.202028.02.2021
11242200401.12.202028.02.2021
11242200411.01.202131.03.2021
11242200421.01.202131.12.2021
11242200431.02.202130.04.2021
11242200441.04.202130.06.2021
11242200451.04.202130.06.2021
11242200461.04.202130.06.2021
11242200471.05.202131.07.2021
11242200481.05.202131.07.2021
11242200491.07.202130.09.2021
11242200501.07.202130.09.2021
11242200511.07.202130.09.2021
11242200521.09.202130.11.2021
11242200531.09.202130.11.2021
11242200541.10.202131.12.2021
11242200551.10.202131.12.2021
11242200561.10.202131.12.2021
11242200571.10.202131.12.2021
11242200581.11.202131.12.2021
11242200591.01.202228.02.2022
11242200601.01.202231.03.2022
11242200611.01.202231.03.2022
11242200621.01.202231.03.2022
11242200631.01.202231.03.2022
11242200641.03.202231.12.2022
Labels:
Message 1 of 5
623 Views
0
1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

‎03-18-2022 02:40 AM

Hi, @alikemalaydin 

Try this:
M query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdZbjoMwDAXQvfBdIdt5r6Xq/rdRBpPEJe70/hF0ZIVcx+L53Jglbo9NhIj4eOCdeBficjwH3ll08Xp8UjG0KqWgizsNK9WqK41Kg62afJqUZkurT7PSMijt1HxalFa7V/JpVdpMVWaftpP2jz5e1p3ORbtTJpzatJqNYKWC07BS/rIBm1azaa004fRKKw5KV8orLTitONW0+vZ0r8WlQqazetXmU8ap4DTgVNPSbv4/WEk4zTgtONW0+KNf/xbHgd9pm/16xtFbe6WBcMpzugxafSo4NWkpvdJyaJyDqFc9T86haQ6iXpX9wwoZpwWndQY79io+NZPwpH28HdfjRiPh1EzC856NYFcqK+UvVa+0ZFDS+eHQOKfLoNmnCacZp2XOrHECxacVp830a99Ac2kinDJOxTQh2yZcacCpmYQ/eiAlnGacFpyaSfiL2kko9sLInWa60XFhVso4FZwGnJq/jE65f9brDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Invoice No" = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start Date", type date}, {"End Date", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Customer ID", Int64.Type}, {"Invoice No", Int64.Type}}),

    startDate=List.Buffer(#"Changed Type"[Start Date]),
    endDate=List.Buffer(#"Changed Type"[End Date]),
    
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Range", each List.Transform({Number.From([Start Date])..Number.From([End Date])}, each Date.From(_))),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Customer ID"}, #"Added Custom1", {"Customer ID"}, "MergeTable", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "AllRange", each List.Union([MergeTable][Range])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Calendar", each List.Transform({Number.From(List.Min(startDate))..Number.From(List.Max(endDate))}, each Date.From(_))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "EmptyDate", each List.RemoveMatchingItems([Calendar],[AllRange])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Range", "MergeTable", "AllRange", "Calendar"}),
    #"Added Custom4" = Table.AddColumn(#"Removed Columns", "Year-Month", each List.Distinct(List.Transform([EmptyDate], each Text.From(Date.Year(_))&"-"&Text.From(Date.Month(_))))),
    #"Added Custom6" = Table.AddColumn(#"Added Custom4", "Count_EmptyDateMonth", each List.Count([#"Year-Month"])),
    #"Added Custom5" = Table.AddColumn(#"Added Custom6", "yyyy-mm", each List.Accumulate([#"Year-Month"], "", (state, current) => state&", "&current)),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Added Custom5", {{"yyyy-mm", each Text.AfterDelimiter(_, " "), type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Extracted Text After Delimiter",{"EmptyDate", "Year-Month"})
in
    #"Removed Columns1"

Result:

vangzhengmsft_0-1647596358749.png

Please refer to the attachment below for details.

Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


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

How to get your questions answered quickly -- How to provide sample data

View solution in original post

W52_How can I find empty dates.pbix
Message 2 of 5
547 Views
0
4 REPLIES 4
v-angzheng-msft
Community Support
Community Support

‎03-18-2022 02:40 AM

Hi, @alikemalaydin 

Try this:
M query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdZbjoMwDAXQvfBdIdt5r6Xq/rdRBpPEJe70/hF0ZIVcx+L53Jglbo9NhIj4eOCdeBficjwH3ll08Xp8UjG0KqWgizsNK9WqK41Kg62afJqUZkurT7PSMijt1HxalFa7V/JpVdpMVWaftpP2jz5e1p3ORbtTJpzatJqNYKWC07BS/rIBm1azaa004fRKKw5KV8orLTitONW0+vZ0r8WlQqazetXmU8ap4DTgVNPSbv4/WEk4zTgtONW0+KNf/xbHgd9pm/16xtFbe6WBcMpzugxafSo4NWkpvdJyaJyDqFc9T86haQ6iXpX9wwoZpwWndQY79io+NZPwpH28HdfjRiPh1EzC856NYFcqK+UvVa+0ZFDS+eHQOKfLoNmnCacZp2XOrHECxacVp830a99Ac2kinDJOxTQh2yZcacCpmYQ/eiAlnGacFpyaSfiL2kko9sLInWa60XFhVso4FZwGnJq/jE65f9brDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Invoice No" = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start Date", type date}, {"End Date", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Customer ID", Int64.Type}, {"Invoice No", Int64.Type}}),

    startDate=List.Buffer(#"Changed Type"[Start Date]),
    endDate=List.Buffer(#"Changed Type"[End Date]),
    
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Range", each List.Transform({Number.From([Start Date])..Number.From([End Date])}, each Date.From(_))),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Customer ID"}, #"Added Custom1", {"Customer ID"}, "MergeTable", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "AllRange", each List.Union([MergeTable][Range])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Calendar", each List.Transform({Number.From(List.Min(startDate))..Number.From(List.Max(endDate))}, each Date.From(_))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "EmptyDate", each List.RemoveMatchingItems([Calendar],[AllRange])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Range", "MergeTable", "AllRange", "Calendar"}),
    #"Added Custom4" = Table.AddColumn(#"Removed Columns", "Year-Month", each List.Distinct(List.Transform([EmptyDate], each Text.From(Date.Year(_))&"-"&Text.From(Date.Month(_))))),
    #"Added Custom6" = Table.AddColumn(#"Added Custom4", "Count_EmptyDateMonth", each List.Count([#"Year-Month"])),
    #"Added Custom5" = Table.AddColumn(#"Added Custom6", "yyyy-mm", each List.Accumulate([#"Year-Month"], "", (state, current) => state&", "&current)),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Added Custom5", {{"yyyy-mm", each Text.AfterDelimiter(_, " "), type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Extracted Text After Delimiter",{"EmptyDate", "Year-Month"})
in
    #"Removed Columns1"

Result:

vangzhengmsft_0-1647596358749.png

Please refer to the attachment below for details.

Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


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

How to get your questions answered quickly -- How to provide sample data
W52_How can I find empty dates.pbix
Message 2 of 5
548 Views
0
alikemalaydin
Frequent Visitor
In response to v-angzheng-msft

‎03-20-2022 10:37 PM

Hi,

 

Thanks for your solution.

I adapted the solution to my tables and controlled the numbers.

 

My new problem is someone invoices do not start the first month of the year or end the last month of the year. 

Exp below: The red ones are overcalculated.

2022-03-21_08-25-45.jpg

Message 3 of 5
534 Views
0
v-angzheng-msft
Community Support
Community Support
In response to alikemalaydin

‎03-20-2022 11:06 PM

Hi, @alikemalaydin 

Based on the information you provided, finding the missing dates is based on all the invoice dates for a user. In my solution, the [yyyy-mm] column is also based on all the invoice dates for a user.
If it is not based on all dates, you cannot get the missing dates for a single invoice.
You don't need to care about the [yyyy-mm] column value for a user's single invoice in power query, you just need to get the results for the card measures.



Best Regards,
Community Support Team _ Zeon Zheng


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

How to get your questions answered quickly -- How to provide sample data
Message 4 of 5
531 Views
0
alikemalaydin
Frequent Visitor
In response to v-angzheng-msft

‎03-20-2022 11:15 PM

Many thanks for your help.

Message 5 of 5
525 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

View All