Repeat a value daily that only occurs monthly

[lasmithfla]

Posted better example of my data and output needed.

Need some help getting a dax calc to have a monthly value repeat daily - I'm not able to attach a file but this is an example of what the data the looks like.  The calc that I wrote is giving me the total of the count instead of the repeating the count.

So in the first line the count should be 857 and Count repeated dialy should also be 857 until you get to 6/1. Then it should update based on those values.

I've hid some of the dates below to save space but basically every day would show.

I do not want to create another table if possible repeating these values; we have years worth of data.

This is what I have written so far that is the 3rd column in the pasted data below.

Count Repeated Daily' = VAR _Weekly = Max('calendar'[StartingDayofMonth])
Return
Calculate (Sum(tableName[Count]),AllSelected('Calendar'[Date]))

Date	Count	Count Repeated Daily
5/1/2023	857	1709
5/2/2023		1709
5/3/2023		1709
5/4/2023		1709
5/5/2023		1709
5/6/2023		1709
5/7/2023		1709
5/22/2023		1709
5/28/2023		1709
5/29/2023		1709
5/30/2023		1709
5/31/2023		1709
6/1/2023	852	1709
6/2/2023		1709
6/3/2023		1709
6/4/2023		1709
6/5/2023		1709
6/6/2023		1709
6/7/2023		1709
6/8/2023		1709
6/9/2023		1709
6/10/2023		1709

[lasmithfla]

HC table date field is Date and only have data for 1st of the month
5/1/2023	857
6/1/2023	852
7/1/2023	906
I need these values to repeat for the each day of the month

From Calendar Table	From HC Table	Desired output with DAX
Date	Count	Count Repeated Daily
05/01/23	857	857
05/02/23		857
05/03/23		857
05/04/23		857
05/05/23		857
05/06/23		857
05/07/23		857
05/27/23		857
05/28/23		857
05/29/23		857
05/30/23		857
05/31/23		857
06/01/23	852	852
06/02/23		852
06/03/23		852
06/04/23		852
06/05/23		852
06/06/23		852
06/07/23		852
06/08/23		852
06/09/23		852
06/10/23		852
06/11/23		852
06/12/23		852
06/13/23		852
06/27/23		852
06/28/23		852
06/29/23		852
06/30/23		852
07/01/23	906	906
07/02/23		906
07/03/23		906

[v-yiruan-msft · Accepted solution]

Hi @lasmithfla ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Count = 
VAR _seldate =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( 'HC'[Value] ),
        FILTER (
            'HC',
            'HC'[Date] >= DATE ( YEAR ( _seldate ), MONTH ( _seldate ), 1 )
                && 'HC'[Date] <= EOMONTH ( _seldate, 0 )
        )
    )

Best Regards

[danextian]

Hi @lasmithfla ,

It seems that what you're trying to achieve is a running sum. If so, try the calculated column below:

Rpeating Value = 
CALCULATE (
    SUM ( 'Table'[Count] ),
    FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
)

[lasmithfla]

The Repeating Value is the answer I'm looking for but my date is in a different column so it wouldn't let me enter that after the "EARLIER" when trying to create a calc. column.  My computer died and just getting reconnected, I will try and put together sample data, I'm not allowed to upload PBIX file using the allowed methods.

[v-yiruan-msft]

Hi @lasmithfla ,

Do you want to get the count of repeated items per month? Could you please provide some raw data in your table with Text format and your expected result with calculation logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards