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.

     

TomMartens's avatar
TomMartens
Icon for Super User rankSuper 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
    Icon for Community Support rankCommunity 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

       

      • Icey's avatar
        Icey
        Icon for Community Support rankCommunity Support
        6 years ago

        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