Forum Discussion

drinko's avatar
drinko
Icon for Helper I rankHelper I
4 years ago
Solved

Measure or Calc Column to have Monthly Amount Columns

I would like to have a Power BI Table with the following Columns: Account | Account Desc | January | February | March ... with the "GL amounts" totaled under each month in the proper columns.  Idea...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    drinko , We can have column in Power query on Dax like

     

     

    DAX - new column

    Month Type = Switch( True(),
    eomonth([Date],0) = eomonth(Today(),0),"CM" ,
    eomonth([Date],0) <  eomonth(Today(),0),"CM-" & datediff(eomonth([Date],0) ,eomonth(Today(),0), Month)
    Format([Date],"MMM-YYYY")
    )

     

    Use matrix visual

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

drinko , We can have column in Power query on Dax like

 

 

DAX - new column

Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),0),"CM" ,
eomonth([Date],0) <  eomonth(Today(),0),"CM-" & datediff(eomonth([Date],0) ,eomonth(Today(),0), Month)
Format([Date],"MMM-YYYY")
)

drinko's avatar
drinko
Icon for Helper I rankHelper I
4 years ago

This worked for the most part and I will mark as solved.  Is there anyway to still have the current month's name listed in the column header instead of having "CM"?  Like have "April" instead of "CM" or "March" instead of "CM-1".

I am basically trying to put data into a Table instead of a Matrix because I don't want the drill-down heirarchy.  I guess another way to solve this is use a Matrix but turn off the drill-down heirarchy.  Is there a way to do that?