Monthly Calculations

[Anonymous]

Hi everyone, sorry if I am posting much lately 😀

For a use case I would like to have some cards with expenses/revenues for specific months in the past. Depending on the data-refresh time I would like to create 2 cards:

Card 1: results from 2 months ago
Card 2: results from 3 months ago

This is what is happening at the moment. We send our data at the end of the month to an accountant, they validate it and give it in in their system and send it to an online application Silverfin. At the moment validating and putting it in the system takes more than 1 month. But in the future this will happen sooner. For example right now we are in march and our up-to-date data are the results from january. BUT if this all happens faster I want to be able to rewrite the code quickly to have more up-to-date cards. for example right now we are in March and we have results from february, if that is the case I would like to have my cards look like:
Card 1: results from the previous month
Card 2: results from 2 months ago

--> But for now let's assume there is a monthly delay, so card 1: results 2 months ago, card 2 results 3 months ago.

This is the measure I have build to calculate total expensen:

TotalExpense = sum('FactSilverfin'[Uitgave])*-1

These are some measurements I have previously seen here but don't seem to work as they return empty values:

ExpensesCurrentMonth = CALCULATE([totalExpense];DATESMTD(DimDate[full date]))

ExpensesLastMonth = CALCULATE([totalExpense];DATESMTD(dateadd(DimDate[full date];-1;MONTH)))

ExpensesSecondLastMonth = CALCULATE([totalExpense];DATESMTD(dateadd(DimDate[full date];-2;MONTH)))

ExpensesThirdLastMonth = CALCULATE([totalExpense];DATESMTD(dateadd(DimDate[full date];-3;MONTH)))

My DimDate table I have downloaded here. My fact table looks like this:

Line Item	Month	Value	True Value	Uitgave (Outcome)	Inkomsten (Income)	Monthname	Level
749000 Diverse bedrijfsopbrengsten	PTD 01/2019	-763,22	-763,22	-763,22	0	dinsdag 1 januari 2019	2
700000 Omzet	PTD 02/2019	2338	2338	0	2338	vrijdag 1 maart 2019	2
602102 Consultant 3	PTD 12/2019	8250	-8250	-8250	0	zondag 1 december 2019	2
612090 Restaurantkosten	PTD 11/2019	661,7	-661,7	-661,7	0	vrijdag 1 november 2019	2
700001Omzet 1	PTD 03/2019	9091,4	9091,4	0	9091,4	vrijdag 1 maart 2019	2

Bonus would be if I also can have some cards with the same data like previous mint, second last month, but from the prevoous year,so maybe like PARALLELPERIOD

Any ideas?

[TomMartens]

Hey @Anonymous ,

navigating through time can become somewhat tedious without a dedicated calendar table, for this I recommend creating a dedicated Calendar table and also create a date column in your table from your Month column.

I assume that you can find almost everything about time calculations in this article by the people from sqlbi.com, even if it's a lengthy article it's a worthy read: https://www.daxpatterns.com/time-patterns/

Regards,

Tom

[Anonymous]

Hi @TomMartens thanks for your answer! Will definitally look into the artice

Is there a difference between the Dimdate I have downloaded from the Kimball website and the dedicated Calendar table you mentioned? Also I have a date column in my facttable at the right,might be confusing as the column is named "MonthName" but this is a date column right?

Thanks

[TomMartens]

Hey  @Anonymous ,

I have no idea if there is a difference between the Calendar table you have downloaded and the Calendar table I mentioned. It's about having a data model (using a separate table) that follows the paradigm of star schema modeling. One aspect might be special to the Power BI data model, if you are using DAX time intelligence functions, these functions require that the Calendar table is complete meaning without gaps at the day level.

Regarding the column Monthname, I have no idea if the column is a date column, as I'm not familiar with your data model, but if this column has the data type date or datetime, maybe this column can be used to relate to the Calendar table.

Regards,

Tom

Regards,

Tom

[Anonymous]

Hi Tom

Yeah I have done this already,so that shouldn't be the issue here.

[Icey · Accepted solution]

Hi @Anonymous ,

As far as I understand, try to do this:

1. I make some changes to your Fact Table.

2. I create a DimDate table, not using yours.

DimDate = CALENDAR ( MIN ( FactSilverfin[Monthname] ), TODAY () )

3. Create relationship.

4. Create your measures and put them in Cards.

For more details, please check the attached PBIX file. 

If I understand you incorrectly, please let me know.

Best Regards,

Icey

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

[Anonymous]

Hi @Icey  thanks for your reply, I still don't understand why it works in your file, and why it won't workin my original file.

Step 1: What exactly did you do here?

Step 2: I have exactly the same column in my original DimDate table

Step 3: I have the same relationship

Step 4: Put my measures in cards an get empty values back

thanks

[Icey]

Hi @Anonymous ,

Step 1: This month is March. But in the sample data you provided, no data for the last three months. So, the results will show as blank. After I changed the date, results show.

I have one question: how do you define the current month? Is it the month in reality or the month you selected?

Best Regards,

Icey

[Anonymous]

@Icey  aah okay, in my real dataset I have data up to january 2020, so my cards ExpensesSecondLastMonth and ExpensesThirdLastMonth should give me some numbers right?

Current month would indeed be the month in reality, so the cards ExpensesCurrentMonth and ExpensesLastMonth should be empty.

--> could be that my measures could be better,I dunno. Pretty new to (Power) BI.

Cheers

[Icey]

Hi @Anonymous ,

Are there any other questions? If not, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Icey

[Anonymous]

@Icey  No thanks, I used a calander table like u said and that seemed to solve the problem. Which is really weird because my other time calculations work fine with my old Dimdate table but yeah. 

For the bonus question (compare results to previous year) I used PARALLELPERIOD

ExpensesSecondLastMonthLastYear = CALCULATE([ExpensesSecondLastMonth];PARALLELPERIOD(DimDateTest[Date];-12;MONTH))