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...
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.

     

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Tom

     

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

     

     

    • Icey's avatar
      Icey
      Community Support
      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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