Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Calendar all the month Using DAX

Dear Team,

 

I've Fact table which have data like

Apple have 12 months data

Orange have 3 months data

Banana have 4 months data

 

if i filter fact table for orange it will show 12 months period instead of 3 months where we don't have data then also it will show on particular month as zero. My table view should be fixed as 12 months.

 

what is DAX Formula ?

 

Orange                     Apple

Need Help

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    Anonymous , You can create new column for this using formula

     

    FixedTable = 
ADDCOLUMNS(
   SUMMARIZE('FactTable', 'FactTable'[Fruit]),
   "Month", IF(COUNTROWS('FactTable') > 0, 'FactTable'[Month], BLANK()),
   "Value", IF(COUNTROWS('FactTable') > 0, 'FactTable'[Value], 0)
)

     and use this table in visualization 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Dear Bhanu,

       

      Thanks for response.

      Value is calculated using measure however this may not works so end up with error

      Measure created like Adj Value = Amount / Qty

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi,

      I've shared the sample data as ideally have 20+ columns of data.

       

      columns

      Month, Type, Value, Qty, GG Value, TR Value, KN Value etc....

       

      Measure field as SUM Of 'ADJ Value' and we need to 12 months report view incase no data on particular months.