Number of days in a month to find daily average

[sivarajan21]

Hi,

I have below sample data set:

Missing Calendar Dates_1 table

Date	DB Name - Points id
Apr 2,2022	inse-1017
Mar 2,2022	inse-1010
jun 2,2022	northyorcc-65
feb 3,2022	gmp-63

I need your help in creating a dax measure/calculated column to find number of days in a month. For example, the above table contains Apr 2,2022 in first row. April 2022 contains 30 days in that month.we need this 30 days to be returned in a measure or column. Similarly,next row contains Mar 2,2022 in 2nd row. March 2022 contains 31 days which the measure has to return.

The expected output could be as below, if it is a calculated column(feel free to use a measure(virtual) as well to return these days):

Date	DB Name - Points id	No. of days
Apr 2,2022	inse-1017	30
Mar 2,2022	inse-1010	31
jun 2,2022	northyorcc-65	30
feb 3,2022	gmp-63	28

Please let me know if you need further info.

Please can you help me on this?

@Ahmedx @Arul @grantsamborn @lbendlin  @amitchandak @Ashish_Mathur @onurbmiguel_ @Greg_Deckler 

Thanks in advance.

[VijayP]

@sivarajan21 

If this is solving your problem statement please Accept my answer as solution!

[sivarajan21]

Hi  ,

That works brilliantly!

You made this look so easy and thanks for this measure.

No of days =

CALCULATE (

COUNTROWS ( 'Calendar' ),

ALL ( 'Calendar' ),

VALUES ( 'Calendar'[Column] )

)

But I need your small help in solving the below which is the last bit we need to do. This involves the measures we had just created.

we have this sample dataset:

Target table

DBName-Point_id	Target type	Value Type	Value
northyorcc-1010	0	Value_01	0
northyorcc-1010	0	Value_02	20
northyorcc-1010	0	Value_03	30.25
northyorcc-1010	0	Value_04	-132
northyorcc-1010	0	Value_05	-2
northyorcc-1010	0	Value_06	0.29
northyorcc-1010	0	Value_07	0.245
northyorcc-1010	0	Value_08	-199
northyorcc-1010	0	Value_09	-187
northyorcc-1010	0	Value_10	-11
northyorcc-1010	0	Value_11	-545
northyorcc-1010	0	Value_12	1
inse-1017	0	Value_01	1
inse-1017	0	Value_02	65

we need to find target unit.for each points id,we need to add numbers in  'Value' column. We should also make sure to add only the numbers that are having 'Target type' as 0 and not 1. After summation, we need to divide those by the measure 'No of days' created above.We need to create a calculated column for this target unit in below sample dataset.

Missing Dates calendar_1

Date	DB Name - Points id
Apr 2,2022	northyorcc-1010
Mar 2,2022	inse-1017

The expected output would look something like below:

Date	DB Name - Points id	Target units
Apr 2,2022	northyorcc-1010	-34.1405
Mar 2,2022	inse-1017	2.096774

Target units column is derived from below formula:

target unit=Sum(value where target type 0)/No of days measure

value is taken from target table given above.

For example, northyorcc-1010 we got -34.1405 using formula as

target unit=-1024.215/30

-1024.215 is sum of value where target type is zero and 30 is no of days in april

Realtionship:

Please let me know if you need further info.

Thanks in advance.

[VijayP · Accepted solution]

@sivarajan21 

You must have a datedimension to get a better result for this

CAlCULATE(COUNTROWS(DatesDim),ALL(DatesDim), VALUES(DateDim[MonthandYear])

So in your Date Table try to add monthandyear column as well!