powerbidaxxlookupcustomcolumn

Selecting column based off of current month

I have an excel spreadsheet of values that gets imported into Power BI. The columns of the spreadsheet are based off the months of the year, and the rows then correlate to user values during that month. I want Power BI to be able to identify which month is the current, and use those values only in the visualizations I tie to that table.

In excel, I did this by adding an additional column with an "xlookup" (Current month being June): enter image description here

The downside of this, is that the "TODAY()" doesn't calculate until the document is opened, meaning that the "Current Month" values don't change until someone opens the document for the first time each month (source: https://community.fabric.microsoft.com/t5/Service/Power-BI-reports-not-updated-with-latest-SharePoint-data/m-p/2315679).

Is there an elegant way to create a custom column in BI that performs the same function, or otherwise to have a visualization display the column information by the current month?

Solution

  • In the following I will name the imported Excel file in Power BI as EXCEL_TABLE.

    You can define a DAX calculated column using the following code:

    Current Month= 
     SWITCH(
          MONTH(TODAY()),         
          1,EXCEL_TABLE[Jan],
          2,EXCEL_TABLE[Feb],
          3,EXCEL_TABLE[Mar],
          4,EXCEL_TABLE[Apr],
          5,EXCEL_TABLE[May],
          6,EXCEL_TABLE[Jun],
          7,EXCEL_TABLE[Jul],
          8,EXCEL_TABLE[Aug],
          9,EXCEL_TABLE[Sep],
          10,EXCEL_TABLE[Oct],
          11,EXCEL_TABLE[Nov],
          12,EXCEL_TABLE[Dec]
     )