Total count of products by day

[KevinPr]

Hi, 

I am trying to show the total count for each subscriptions for each day of the month.  The report is showing the right grand total, but the days of the month is showing the I thinking the total subscriptions sold on that day of the month over time.

DAX Code-

Packages =

CALCULATE(

    DISTINCTCOUNT(Subscription_Data_171[Standard_Sales_Code]),

        Filter(

            CROSSJOIN(

            VALUES(Subscription_Data_171),

            VALUES(ItemCategories_List[Code])

            ),

             (

             YEAR(Subscription_Data_171[Contract_End_Date]) < 1950 ||

             Subscription_Data_171[Contract_End_Date]> DATE(YEAR(MAX(Subscription_Data_171[Contract_Start_Date])),MONTH(MAX('Subscription_Data_171'[Contract_Start_Date])),1)

           &&

             Subscription_Data_171[Contract_Start_Date] <= DATE(YEAR(Max(Subscription_Data_171[Contract_Start_Date])),MONTH(Max(Subscription_Data_171[Contract_Start_Date])),31) &&

             YEAR(Subscription_Data_171[Contract_Start_Date]) > 1950 &&

            ItemCategories_List[Code] = "DIGITALTV"

              )

            )

    )

Result

The total on each day should be close to the grand total. 

Example: at the start of the month the 210 could have been 200.  

Da1 = 200 .... Day 31 = 210.

Thank you,

This my first post and I am new to learning power bi & dax. 

@count

[Ashish_Mathur]

Hi,

Share some data to work with, explain the question and show the expected result.  With just a measure that you have posted, it is nt possible to understand your requirement.

[KevinPr]

Thank you for replying. 

- The data for the report is coming from Business Central - Subscription Data table

- I am using a crossjoin to connect the ItemCategories_List table to filter the subscrption Group.

- The report should show the peek total number of subscriptions per month for the selected year

- To determine if a subscrption is active I am using the Contract Start date and Contract End date from the Subscription Data table

* If the contract started before the first day of the month & the contract end date is before the last day of the month then count the subscription. 

Screen shot of Subscription Data table

Sample result. Shos the total for each month. 

Subscription	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
BUNDLEBASICBILINGUAL	195	199	195	198	197	198	198	199	200	205	210	210
BUNDLEBASICFRANCOTV	62	60	68	68	65	62	68	69	69	70	71	72
BUNDLECOMPLETETV	41	41	45	46	48	47	42	40	45	8	49	51
NOBUNBASICBILINGUATV	20	20	22	24	27	26	20	24	28	28	29	28

I hope this helps clarify and Thank you for the support. 

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached file.

Hope this helps.