CODEWITHSUNDEEP
sql-serverdaxssas-tabular
jcam77
jcam77

Reputation: 173

Get total sum for each month in DAX

I have a tabular mode analysis server connected to a sql server database. I want to get the total x per month, and I have the total x per day. So for example, I have a table DailyEvent with the first 2 columns like this, and I want the column "MonthXCount":

TimeID   XCount MonthXCount
20160429  3          11
20160430  8          11
20160501  4           4

So the total XCount for April is 11, so for every day in April I want 11. The total X count for may is 4 (so far).

What I have now is a MonthToDate total I think, calculated as:

=TOTALMTD(SUM('DailyEvent'[XCount]),'DailyEvent'[TimeID])

But I want to then have a column that puts the last value of the month in for every day of the month.

The end goal is to have a XCount by month graph in PowerBI, so I might not need to add the column here... I might be able to just tell PowerBi to graph the last day of the month, but I'm not sure how to do that and thought this would be easier.

Upvotes: 1

Views: 13777

Answers (3)

Dariusz Bielak
Dariusz Bielak

Reputation: 415

You could use a measure calculation in Power BI, or in SSAS model, if there is a calendar table, make sure it is marked as date table otherwise the time logic will not work. I used the following query (DAX) it calculates the Month To Date

MtD :=CALCULATE(SUM('DailyEvent'[XCount]),DATESMTD('DateTabe'[calendarDate]))

If however there is no calendar or you are working with the be-spoke calendar, this may help Running Totals Whitout a Traditional Calendar Table

Upvotes: 0

Orlando Mezquita
Orlando Mezquita

Reputation: 153

Do you have a Calendar Table in your model connected to your data table?

If yes, let's say the Calendar Table is called DimDate and that it contains a column called YearMonth, then the formula would be:

Month Sales := SUMX(
                    VALUES(DimDate[YearMonth]),
                    SUM(DailyEvent[XCount])
                    )

If you don't have a Calendar Table, then you can create a calculated column in your table called YearMonth with this formula:

=LEFT(DailyEvent[TimeID], 6)

Then calculate the sum of Month Sales with:

Month Sales := SUMX(
                    VALUES(DailyEvent[YearMonth]),
                    SUM(DailyEvent[XCount])
                    )

Hope this helps!

A note with respect to the formula you were using: Time intelligence functions, such as TOTALMTD, require Calendar Tables. Therefore, be sure to add one to your data model before using them.

Edit:

Another solution could be to create a date column:

     = DATE(
            LEFT(DailyEvent[TimeID], 4), 
            MID(DailyEvent[TimeID], 5, 2), 
            1)

Then drop that column in the X-axis of the graph and the XCount column in the Y-axis.

Upvotes: 0

WickedFan
WickedFan

Reputation: 356

SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;

cmd.CommandText = "begin transaction t    
    update table dailyevent
    set monthxcount = (select top(1) monthxcount 
                            from dailyevent 
                            where MONTH(timeID) = 4 
                            order by monthxcount desc)
    where MONTH(timeID) = 4

    --commit only if the rows affected corresponds to the number of rows found for the month of april.

    commit";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

reader = cmd.ExecuteReader();
// Data is accessible through the DataReader object here.

sqlConnection1.Close();

Upvotes: 0

Related Questions