Calculate Measure to show number of items fall within a month

[Sachintha]

I have a data set that shows warranty information for a set of machines. A machine name, and two dates; warranty start and end dates. Sample data set posted at the end of this post.

I want to create a bar chart showing the number of machines under warrenty month over month from this data set. It would look something like this (not actual numbers, just a dummy chart to give you the idea). This neats to adhere to all the filter selections and such.

What's the best way to go about this? I'm assuming I'm giong to need to create a DateTable and some sort of a measure to count the machines under warrenty for the period (say, month) in question. But I'm facing several problems. For example:

1. How would I link the DateTable with this? I have two date columns in this table, not one.
2. How would the measure count the machines under warranty based on the X-axis unit? Ideally, I'd like to have a date hierarchy for the X-axis, and based on user actions/drill down, it'll show the correct number of machines under warranty.

---------------------------------------------------------------------------------------------------

EDIT FOR CLARIFICATION

I think I didn't clearly explain the requirement, so let me illustrate with a smaller data set that's easy to visualize. Let's assume that all machines' warranty start on the same day (2024/1/1), and that their warrant ends on different days. Please consider the following data set:

Machine,Warranty Start,Warranty End
M001,2024-01-01,2024-02-10
M002,2024-01-01,2024-05-29
M003,2024-01-01,2024-07-01
M004,2024-01-01,2024-08-08
M005,2024-01-01,2024-08-21
M006,2024-01-01,2024-09-24
M007,2024-01-01,2024-09-25
M008,2024-01-01,2024-11-09
M009,2024-01-01,2024-11-26
M010,2024-01-01,2024-12-14

The first 'Warranty End Date' is on February 10th, so what this means is for the month of January, all 10 machines are under warranty. So, in our chart, for the month 2024/January, we should have the value 10. Then, M001 warranty ends on February, so let's still count that as under warranty for the whole month of February, which means the value for 2024/February should also be 10. But when we reach March, the M001 machine is no longer under warranty, and the value for 2024/March should now be 9, as only 9 machines are under warranty at that point in time. Following along this logic, this should be the chart that's produced.

Essentially, I want to know the total number of machines that's under warranty by a given day/month/year. 

-----------------------------------------------------------------------------------------------

Machine,Warranty Start,Warranty End
M001,2026-07-22,2027-07-22
M002,2026-06-25,2027-06-25
M003,2026-06-25,2027-06-25
M004,2026-03-19,2028-03-18
M005,2025-09-16,2027-09-16
M006,2025-08-21,2026-08-21
M007,2025-06-24,2027-06-24
M008,2025-06-22,2027-06-22
M009,2025-06-19,2027-06-19
M010,2025-05-27,2027-05-27
M011,2025-05-20,2026-05-20
M012,2025-05-20,2026-05-20
M013,2025-05-20,2026-05-20
M014,2025-05-20,2026-05-20
M015,2025-05-07,2027-05-07
M016,2025-04-25,2027-04-25
M017,2025-04-25,2026-04-25
M018,2025-04-24,2026-04-24
M019,2025-04-24,2026-04-24
M020,2025-04-24,2026-04-24
M021,2025-04-23,2027-04-23
M022,2025-04-22,2027-04-22
M023,2025-04-22,2027-04-22
M024,2025-04-18,2027-04-18
M025,2025-04-15,2027-04-15
M026,2025-04-13,2027-04-13
M027,2025-04-11,2026-04-11
M028,2025-04-10,2027-04-10
M029,2025-04-07,2027-04-07
M030,2025-03-28,2026-03-28
M031,2025-03-28,2026-03-28
M032,2025-03-28,2026-03-28
M033,2025-03-27,2026-03-27
M034,2025-03-27,2027-03-27
M035,2025-03-26,2027-03-26
M036,2025-03-25,2027-03-25
M037,2025-03-25,2026-03-25
M038,2025-03-23,2027-03-23
M039,2025-03-23,2026-03-23
M040,2025-03-22,2027-03-22
M041,2025-03-21,2027-03-21
M042,2025-03-20,2026-03-20
M043,2025-03-20,2027-03-20
M044,2025-03-18,2027-03-18
M045,2025-03-15,2027-03-15
M046,2025-03-12,2027-03-12
M047,2025-03-12,2027-03-12
M048,2025-03-10,2027-03-10
M049,2025-03-08,2027-03-08
M050,2025-03-04,2027-03-04
M051,2025-02-28,2026-02-28
M052,2025-02-28,2026-02-28
M053,2025-02-26,2026-02-26
M054,2025-02-25,2027-02-25
M055,2025-02-22,2026-02-22
M056,2025-02-22,2026-02-22
M057,2025-02-22,2026-02-22
M058,2025-02-21,2026-02-21
M059,2025-02-15,2027-02-15
M060,2025-01-26,2027-01-26
M061,2024-12-24,2025-12-24
M062,2024-12-23,2025-12-23
M063,2024-12-17,2026-12-17
M064,2024-12-16,2025-12-16
M065,2024-12-15,2026-12-15
M066,2024-12-02,2026-12-02
M067,2024-11-29,2026-11-29
M068,2024-11-23,2025-11-23
M069,2024-11-21,2025-11-21
M070,2024-11-21,2025-11-21
M071,2024-10-23,2025-10-23
M072,2024-10-20,2026-10-20
M073,2024-10-19,2025-10-19
M074,2024-10-19,2025-10-19
M075,2024-10-03,2026-10-03
M076,2024-09-28,2025-09-28
M077,2024-09-27,2025-09-27
M078,2024-09-21,2025-09-21
M079,2024-09-21,2025-09-21
M080,2024-09-18,2025-09-18
M081,2024-09-18,2025-09-18
M082,2024-09-17,2026-09-17
M083,2024-09-15,2025-09-15
M084,2024-09-01,2026-09-01
M085,2024-08-30,2025-08-30
M086,2024-08-29,2025-08-29
M087,2024-08-16,2025-08-16
M088,2024-08-13,2025-08-13
M089,2024-08-13,2025-08-13
M090,2024-08-12,2025-08-12
M091,2024-08-12,2025-08-12
M092,2024-08-08,2026-08-08
M093,2024-08-08,2025-08-08
M094,2024-08-06,2026-08-06
M095,2024-08-02,2025-08-02
M096,2024-07-26,2025-07-26
M097,2024-07-17,2026-07-17
M098,2024-07-12,2025-07-12
M099,2024-07-06,2025-07-06
M100,2024-07-04,2025-07-04
M101,2024-06-29,2026-06-29
M102,2024-06-29,2025-06-29
M103,2024-06-28,2026-06-28
M104,2024-06-28,2025-06-28
M105,2024-06-27,2025-06-27
M106,2024-06-22,2025-06-22
M107,2024-06-18,2025-06-18
M108,2024-06-18,2025-06-18
M109,2024-06-15,2025-06-15
M110,2024-06-15,2025-06-15
M111,2024-06-15,2025-06-15
M112,2024-06-08,2025-06-08
M113,2024-06-08,2025-06-08
M114,2024-06-08,2025-06-08
M115,2024-06-07,2025-06-07
M116,2024-05-27,2025-05-27
M117,2024-05-24,2025-05-24
M118,2024-05-24,2025-05-24
M119,2024-05-12,2025-05-12
M120,2024-04-24,2025-04-24
M121,2024-04-24,2025-04-24
M122,2024-04-20,2025-04-20
M123,2024-04-16,2025-04-16
M124,2024-04-03,2025-04-03
M125,2024-03-31,2025-03-31
M126,2024-03-27,2025-03-27
M127,2024-03-23,2025-03-23
M128,2024-03-23,2025-03-23
M129,2024-03-20,2025-03-20
M130,2024-03-19,2025-03-19
M131,2024-03-09,2025-03-09
M132,2024-02-29,2025-02-28
M133,2024-02-28,2025-02-27
M134,2024-02-26,2025-02-25
M135,2024-02-25,2026-02-24
M136,2024-02-23,2025-02-22
M137,2024-02-23,2025-02-22
M138,2024-02-22,2025-02-21
M139,2024-02-18,2025-02-17
M140,2024-02-16,2025-02-15
M141,2024-02-13,2025-02-12
M142,2024-02-10,2025-02-09
M143,2024-02-10,2025-02-09
M144,2024-02-10,2025-02-09
M145,2024-02-09,2025-02-08
M146,2024-02-07,2025-02-06
M147,2024-02-06,2025-02-05
M148,2024-02-05,2025-02-04
M149,2024-02-05,2025-02-04
M150,2024-02-05,2025-02-04
M151,2024-02-04,2025-02-03
M152,2024-02-03,2025-02-02
M153,2024-02-02,2025-02-01
M154,2024-02-01,2025-01-31
M155,2024-01-31,2025-01-30
M156,2024-01-30,2025-01-29
M157,2024-01-26,2025-01-25
M158,2024-01-25,2025-01-24
M159,2024-01-25,2025-01-24
M160,2024-01-24,2025-01-23
M161,2024-01-23,2025-01-22
M162,2024-01-21,2025-01-20
M163,2024-01-21,2025-01-20
M164,2024-01-20,2025-01-19
M165,2024-01-18,2025-01-17
M166,2024-01-17,2025-01-16
M167,2024-01-17,2025-01-16
M168,2024-01-13,2025-01-12
M169,2024-01-07,2025-01-06
M170,2023-12-31,2024-12-30
M171,2023-12-29,2024-12-28
M172,2023-12-26,2024-12-25
M173,2023-12-20,2025-12-19
M174,2023-12-10,2025-12-09
M175,2023-12-05,2024-12-04
M176,2023-12-01,2024-11-30
M177,2023-11-29,2025-11-28
M178,2023-11-25,2024-11-24
M179,2023-11-23,2024-11-22
M180,2023-11-23,2024-11-22
M181,2023-11-12,2024-11-11
M182,2023-11-09,2024-11-08
M183,2023-11-08,2024-11-07
M184,2023-11-08,2024-11-07
M185,2023-11-01,2024-10-31
M186,2023-10-31,2024-10-30
M187,2023-10-26,2024-10-25
M188,2023-10-25,2024-10-24
M189,2023-10-24,2025-10-23
M190,2023-10-22,2024-10-21
M191,2023-10-19,2024-10-18
M192,2023-10-19,2024-10-18
M193,2023-10-17,2024-10-16
M194,2023-10-16,2024-10-15
M195,2023-10-15,2024-10-14
M196,2023-10-15,2024-10-14
M197,2023-10-15,2024-10-14
M198,2023-10-15,2024-10-14
M199,2023-10-14,2024-10-13
M200,2023-10-12,2024-10-11
M201,2023-10-10,2024-10-09
M202,2023-10-08,2024-10-07
M203,2023-10-05,2024-10-04
M204,2023-10-03,2024-10-02
M205,2023-10-02,2024-10-01
M206,2023-10-01,2024-09-30
M207,2023-09-29,2024-09-28
M208,2023-09-28,2024-09-27
M209,2023-09-27,2024-09-26
M210,2023-09-26,2024-09-25
M211,2023-09-25,2024-09-24
M212,2023-09-25,2024-09-24
M213,2023-09-24,2024-09-23
M214,2023-09-23,2024-09-22
M215,2023-09-23,2024-09-22
M216,2023-09-23,2024-09-22
M217,2023-09-23,2024-09-22
M218,2023-09-22,2024-09-21
M219,2023-09-19,2024-09-18
M220,2023-09-18,2024-09-17
M221,2023-09-17,2024-09-16
M222,2023-09-16,2024-09-15
M223,2023-09-15,2024-09-14
M224,2023-09-15,2024-09-14
M225,2023-09-14,2024-09-13
M226,2023-09-13,2024-09-12
M227,2023-09-13,2024-09-12
M228,2023-09-12,2024-09-11
M229,2023-09-07,2024-09-06
M230,2023-09-07,2024-09-06
M231,2023-09-03,2024-09-02
M232,2023-09-03,2024-09-02
M233,2023-09-02,2024-09-01
M234,2023-09-01,2024-08-31
M235,2023-08-31,2025-08-30
M236,2023-08-28,2024-08-27
M237,2023-08-26,2024-08-25
M238,2023-08-26,2024-08-25
M239,2023-08-20,2024-08-19
M240,2023-08-20,2024-08-19
M241,2023-08-19,2024-08-18
M242,2023-08-15,2024-08-14
M243,2023-08-14,2025-08-13
M244,2023-08-03,2024-08-02
M245,2023-07-29,2024-07-28
M246,2023-07-26,2024-07-25
M247,2023-07-22,2025-07-21
M248,2023-07-21,2025-07-20
M249,2023-07-20,2024-07-19
M250,2023-07-06,2024-07-05
M251,2023-06-26,2024-06-25
M252,2023-06-25,2024-06-24
M253,2023-06-17,2024-06-16
M254,2023-05-22,2024-05-21
M255,2023-05-17,2024-05-16
M256,2023-05-17,2024-05-16
M257,2023-05-04,2025-05-03
M258,2023-04-29,2025-04-28
M259,2023-04-06,2024-04-05
M260,2023-04-06,2024-04-05
M261,2023-03-29,2025-03-28
M262,2023-03-11,2025-03-10
M263,2023-03-08,2025-03-07
M264,2023-03-04,2025-03-03
M265,2023-03-03,2025-03-02
M266,2023-03-02,2025-03-01
M267,2023-03-02,2024-03-01
M268,2023-02-28,2024-02-28
M269,2023-02-28,2024-02-28
M270,2023-02-28,2024-02-28
M271,2023-02-21,2025-02-20
M272,2023-02-12,2025-02-11
M273,2023-02-11,2024-02-11
M274,2023-02-09,2025-02-08
M275,2023-01-30,2025-01-29
M276,2023-01-26,2025-01-25
M277,2023-01-26,2025-01-25
M278,2023-01-22,2025-01-21
M279,2023-01-19,2025-01-18
M280,2023-01-16,2025-01-15
M281,2023-01-16,2025-01-15
M282,2023-01-12,2024-01-12
M283,2023-01-10,2025-01-09
M284,2023-01-03,2025-01-02
M285,2023-01-03,2024-01-03
M286,2023-01-03,2024-01-03
M287,2023-01-03,2024-01-03
M288,2022-12-23,2023-12-23
M289,2022-12-18,2024-12-17
M290,2022-12-01,2023-12-01
M291,2022-12-01,2023-12-01
M292,2022-12-01,2023-12-01
M293,2022-11-27,2023-11-27
M294,2022-11-24,2023-11-24
M295,2022-11-24,2023-11-24
M296,2022-11-22,2024-11-21
M297,2022-11-21,2024-11-20
M298,2022-11-20,2024-11-19
M299,2022-11-09,2024-11-08
M300,2022-11-07,2024-11-06
M301,2022-10-27,2024-10-26
M302,2022-10-27,2024-10-26
M303,2022-10-26,2024-10-25
M304,2022-10-23,2024-10-22
M305,2022-10-16,2024-10-15
M306,2022-10-16,2024-10-15
M307,2022-10-15,2024-10-14
M308,2022-10-15,2024-10-14
M309,2022-10-14,2024-10-13
M310,2022-10-13,2023-10-13
M311,2022-10-13,2023-10-13
M312,2022-10-11,2024-10-10
M313,2022-10-08,2024-10-07
M314,2022-09-30,2023-09-30
M315,2022-09-23,2024-09-22
M316,2022-09-23,2024-09-22
M317,2022-09-22,2024-09-21
M318,2022-09-15,2024-09-14
M319,2022-09-14,2024-09-13
M320,2022-09-14,2024-09-13
M321,2022-09-12,2024-09-11
M322,2022-09-06,2024-09-05
M323,2022-09-02,2024-09-01
M324,2022-08-29,2024-08-28
M325,2022-08-25,2023-08-25
M326,2022-08-24,2024-08-23
M327,2022-08-17,2024-08-16
M328,2022-07-23,2023-07-23
M329,2022-07-20,2024-07-19
M330,2022-07-11,2024-07-10
M331,2022-07-10,2024-07-09
M332,2022-06-30,2024-06-29
M333,2022-06-29,2023-06-29
M334,2022-06-14,2023-06-14
M335,2022-06-14,2024-06-13
M336,2022-06-12,2024-06-11
M337,2022-06-12,2023-06-12
M338,2022-06-09,2023-06-09
M339,2022-06-05,2023-06-05
M340,2022-06-03,2024-06-02
M341,2022-05-30,2023-05-30
M342,2022-05-30,2024-05-29
M343,2022-05-29,2023-05-29
M344,2022-05-27,2024-05-26
M345,2022-05-24,2024-05-23
M346,2022-05-17,2023-05-17
M347,2022-05-15,2024-05-14
M348,2022-05-08,2024-05-07
M349,2022-04-21,2023-04-21
M350,2022-04-12,2024-04-11
M351,2022-04-10,2023-04-10
M352,2022-04-03,2023-04-03
M353,2022-03-25,2023-03-25
M354,2022-03-23,2023-03-23
M355,2022-03-20,2024-03-19
M356,2022-03-04,2024-03-03
M357,2022-03-03,2024-03-02
M358,2022-02-25,2024-02-25
M359,2022-02-16,2024-02-16
M360,2022-02-14,2024-02-14
M361,2022-02-14,2024-02-14
M362,2022-02-10,2024-02-10
M363,2022-02-08,2024-02-08
M364,2022-02-01,2024-02-01
M365,2022-01-21,2023-01-21
M366,2022-01-20,2024-01-20
M367,2022-01-18,2023-01-18
M368,2022-01-05,2024-01-05
M369,2022-01-02,2024-01-02
M370,2021-12-30,2022-12-30
M371,2021-12-24,2022-12-24
M372,2021-12-24,2022-12-24
M373,2021-12-22,2022-12-22
M374,2021-12-18,2022-12-18
M375,2021-12-18,2022-12-18
M376,2021-12-04,2023-12-04
M377,2021-12-03,2023-12-03
M378,2021-12-02,2022-12-02
M379,2021-12-01,2023-12-01
M380,2021-12-01,2023-12-01
M381,2021-11-28,2022-11-28
M382,2021-11-25,2022-11-25
M383,2021-11-25,2022-11-25
M384,2021-11-22,2023-11-22
M385,2021-11-21,2022-11-21
M386,2021-11-15,2022-11-15
M387,2021-11-04,2022-11-04
M388,2021-10-30,2023-10-30
M389,2021-10-22,2022-10-22
M390,2021-10-22,2022-10-22
M391,2021-10-15,2022-10-15
M392,2021-10-12,2023-10-12
M393,2021-09-30,2023-09-30
M394,2021-09-30,2022-09-30
M395,2021-09-24,2022-09-24
M396,2021-09-23,2022-09-23
M397,2021-09-22,2023-09-22
M398,2021-09-19,2022-09-19
M399,2021-09-19,2023-09-19
M400,2021-09-12,2023-09-12
M401,2021-08-08,2022-08-08
M402,2021-07-25,2023-07-25
M403,2021-07-22,2022-07-22
M404,2021-07-09,2022-07-09
M405,2021-07-09,2023-07-09
M406,2021-07-07,2022-07-07
M407,2021-06-29,2022-06-29
M408,2021-06-25,2022-06-25
M409,2021-05-26,2022-05-26
M410,2021-05-22,2022-05-22
M411,2021-05-01,2022-05-01
M412,2021-04-30,2022-04-30
M413,2021-04-30,2023-04-30
M414,2021-04-24,2023-04-24
M415,2021-04-22,2022-04-22
M416,2021-04-16,2022-04-16
M417,2021-04-09,2023-04-09
M418,2021-03-28,2022-03-28
M419,2021-03-24,2022-03-24
M420,2021-03-18,2023-03-18
M421,2021-02-21,2023-02-21
M422,2021-02-12,2022-02-12
M423,2021-02-12,2022-02-12
M424,2021-02-11,2023-02-11
M425,2021-02-04,2022-02-04
M426,2021-01-30,2023-01-30
M427,2021-01-28,2023-01-28
M428,2021-01-27,2022-01-27
M429,2021-01-23,2023-01-23
M430,2021-01-22,2023-01-22
M431,2021-01-14,2022-01-14
M432,2021-01-08,2022-01-08
M433,2020-12-28,2021-12-28
M434,2020-12-25,2022-12-25
M435,2020-12-25,2022-12-25
M436,2020-12-23,2021-12-23
M437,2020-12-22,2021-12-22
M438,2020-12-22,2021-12-22
M439,2020-12-22,2021-12-22
M440,2020-12-19,2022-12-19
M441,2020-12-14,2022-12-14
M442,2020-11-27,2022-11-27
M443,2020-11-25,2021-11-25
M444,2020-11-24,2021-11-24
M445,2020-11-18,2022-11-18
M446,2020-11-17,2022-11-17
M447,2020-11-17,2022-11-17
M448,2020-11-17,2022-11-17
M449,2020-11-16,2022-11-16

[Sachintha]

@parry2k thanks for the pretty prompt response.

I understand the first part, I was intending to create a DateTable, but was not sure how to link both columns to it. Seems like I don't need to, that I can only link one date column, so I went with just the Warranty End Date.

The measure itself, however, isn't quite doing what I want. 

I think I didn't clearly explain the requirement, so let me illustrate with a smaller data set that's easy to visualize. Let's assume that all machines' warranty start on the same day (2024/1/1), and that their warrant ends on different days. Please consider the following data set:

Machine,Warranty Start,Warranty End
M001,2024-01-01,2024-02-10
M002,2024-01-01,2024-05-29
M003,2024-01-01,2024-07-01
M004,2024-01-01,2024-08-08
M005,2024-01-01,2024-08-21
M006,2024-01-01,2024-09-24
M007,2024-01-01,2024-09-25
M008,2024-01-01,2024-11-09
M009,2024-01-01,2024-11-26
M010,2024-01-01,2024-12-14

Using your measure, this is the chart I get with the above smaller set of data:

But that's not quite what I need. It seems to show the number of machines whose warranty ends on a given month (or year, if I drill up the hierarchy).

Please consider the following explanation.

The first 'Warranty End Date' is on February 10th, so what this means is for the month of January, all 10 machines are under warranty. So, in our chart, for the month 2024/January, we should have the value 10. Then, M001 warranty ends on February, so let's still count that as under warranty for the whole month of February, which means the value for 2024/February should also be 10. But when we reach March 1st, that machine is no longer under warranty, and the value for 2024/March should now be 9, as only 9 machines are under warranty at that point in time. Following along this logic, this should be the chart that's produced.

Essentially, I want to know the total number of machines that's under warranty by a given day/month/year. 

[Anonymous · Accepted solution]

Hi @Sachintha ,

I made simple samples and you can check the results below:

DateTable = 
ADDCOLUMNS (
    CALENDAR (DATE(2024, 1, 1), DATE(2024, 12, 31)),
    "Year", YEAR([Date]),
    "MonthNumber", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "YearMonth", FORMAT([Date], "YYYY-MM")
)

MachinesUnderWarranty = 
VAR CurrentDate = MAX(DateTable[Date])
RETURN
CALCULATE (
    COUNTROWS('Table'),
    FILTER (
        'Table',
        'Table'[Warranty Start] <= CurrentDate &&
        'Table'[Warranty End] >= CurrentDate
    )
)

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Sachintha]

@Anonymous thank you, this is exactly what I needed.

I did realize that to get the chart I want (the number of machines under warranty at the beginning of the month), I need to use MIN() instead of MAX(), but otherwise it works perfect.

Can you please explain the code as well? When you take MIN or MAX, and plot it against months, for each month does it grab either 1st or last day of that month?

[Anonymous]

Hi @Sachintha ,

It's neither, it's more like a dynamic continuum of dates.

Best regards,
Community Support Team_ Scott Chang

[parry2k]

@Sachintha y

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

Create following measure:

Machines Under Warranty = 
VAR __MinDate = MIN ( 'Calendar'[Date] )
VAR __MaxDate = MAX ( 'Calendar'[Date] )
RETURN
SUMX ( 
    FILTER ( 
        'Table',
        (
            'Table'[Warranty Start] >= __MinDate &&
            'Table'[Warranty Start] <= __MaxDate
        ) 
        ||
        (        
            'Table'[Warranty End] >= __MinDate &&
            'Table'[Warranty End] <= __MaxDate
        )

    ), 
    1
)

To visualize, use column from date table and above measure and that should do it.