Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Monthly Calculations

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 ItemMonthValueTrue ValueUitgave (Outcome)Inkomsten (Income)MonthnameLevel
749000 Diverse bedrijfsopbrengstenPTD 01/2019-763,22-763,22-763,220dinsdag 1 januari 20192
700000 OmzetPTD 02/20192338233802338vrijdag 1 maart 20192
602102 Consultant 3PTD 12/20198250-8250-82500zondag 1 december 20192
612090 RestaurantkostenPTD 11/2019661,7-661,7-661,70vrijdag 1 november 20192
700001Omzet 1PTD 03/20199091,49091,409091,4vrijdag 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?

 

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

     

10 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        TomMartens
        Super User
        6 years ago

        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